做网站公司做网站公司,网络优化工程师前景,世纪城网站建设,网站建设属于什么服务为什么需要使用外表
在日常的业务场景中#xff0c;经常遇到需要在数据库中处理外部数据的情况#xff0c;这些数据可能来源于应用程序#xff0c;或者是其他业务系统。一般来说#xff0c;常是通过ETL工具将外部数据库的数据导入到数据库内部的表中#xff0c;再进行分析…为什么需要使用外表
在日常的业务场景中经常遇到需要在数据库中处理外部数据的情况这些数据可能来源于应用程序或者是其他业务系统。一般来说常是通过ETL工具将外部数据库的数据导入到数据库内部的表中再进行分析处理。而通过外部表则可以直接访问外部文件并读取外部数据文件进行处理这样做有几个好处
可以减少数据的拷贝节省数据库存储空间。提高数据的共享避免数据出现不一致的情况。删除外表时原库的数据不会被删除。
此外外部表相比普通表具有更丰富的功能
支持多种存储方式例如数据文件可以放在不同云服务的对象存储服务中。支持多种存储格式例如CSV格式。
需要额外说明的是外表不支持DML。 外表的用法示例 步骤1: 准备外部表数据
我们在阿里云的对象存储OSS中存放了TPCH 1G的数据其中lineitem的表的数据分成了10个文件放在mydata/tpch_1g_data/lineitem中
object list is:
71.96MB Standard oss://mydata/tpch_1g_data/lineitem/lineitem.tbl.1
72.63MB Standard oss://mydata/tpch_1g_data/lineitem/lineitem.tbl.10
72.10MB Standard oss://mydata/tpch_1g_data/lineitem/lineitem.tbl.2
72.57MB Standard oss://mydata/tpch_1g_data/lineitem/lineitem.tbl.3
72.51MB Standard oss://mydata/tpch_1g_data/lineitem/lineitem.tbl.4
72.57MB Standard oss://mydata/tpch_1g_data/lineitem/lineitem.tbl.5
72.72MB Standard oss://mydata/tpch_1g_data/lineitem/lineitem.tbl.6
72.48MB Standard oss://mydata/tpch_1g_data/lineitem/lineitem.tbl.7
72.60MB Standard oss://mydata/tpch_1g_data/lineitem/lineitem.tbl.8
72.53MB Standard oss://mydata/tpch_1g_data/lineitem/lineitem.tbl.9 步骤2: 创建外表
外表的用法和普通表非常相似比普通表多了 LOCATION 和 FORMAT 属性。其中 LOCATION 用于指定数据文件所在位置FORMAT 指定数据文件的格式。
CREATE EXTERNAL TABLE lineitem
(L_ORDERKEY int,L_PARTKEY int,L_SUPPKEY int,L_LINENUMBER int,L_QUANTITY DECIMAL(15,2),L_EXTENDEDPRICE DECIMAL(15,2),L_DISCOUNT DECIMAL(15,2),L_TAX DECIMAL(15,2),L_RETURNFLAG CHAR(1),L_LINESTATUS CHAR(1),L_SHIPDATE DATE,L_COMMITDATE DATE,L_RECEIPTDATE DATE,L_SHIPINSTRUCT CHAR(25),L_SHIPMODE CHAR(10),L_COMMENT VARCHAR(44)
)
LOCATION oss://$ACCESS_ID:$ACCESS_KEY$HOST/tpch_1g_data/lineitem/
FORMAT (TYPE CSVFIELD_DELIMITER |
); 如果文件中的列顺序和表中的列顺序不一致可以通过通过metadata$filecolN 伪列进行对应具体可以参考 用户文档 步骤3: 查看外表的文件
外表创建时会将LOCATION下的文件列表保存在一个文件列表中外表扫描时只会访问这个列表下的外部文件。 通过以下语句可以查看外表的文件列表
select * from DBA_EXTERNAL_TABLE_FILES where table_name lineitem; 当外部数据文件有变化时可以执行以下语句更新外表的文件列表
alter external table lineitem refresh; 如果文件被删除且未更新文件列表外表查询时会自动忽略这个文件。 步骤4: 查询外表
外表查询时通过外表的驱动层直接读取外部文件并按照文件格式进行解析转换成OceanBase内部的数据类型后返回数据行。
obclientselect * from lineitem limit 10;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| L_ORDERKEY | L_PARTKEY | L_SUPPKEY | L_LINENUMBER | L_QUANTITY | L_EXTENDEDPRICE | L_DISCOUNT | L_TAX | L_RETURNFLAG | L_LINESTATUS | L_SHIPDATE | L_COMMITDATE | L_RECEIPTDATE | L_SHIPINSTRUCT | L_SHIPMODE | L_COMMENT |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | 155190 | 7706 | 1 | 17.00 | 21168.23 | 0.04 | 0.02 | N | O | 1996-03-13 | 1996-02-12 | 1996-03-22 | DELIVER IN PERSON | TRUCK | egular courts above the |
| 1 | 67310 | 7311 | 2 | 36.00 | 45983.16 | 0.09 | 0.06 | N | O | 1996-04-12 | 1996-02-28 | 1996-04-20 | TAKE BACK RETURN | MAIL | ly final dependencies: slyly bold |
| 1 | 63700 | 3701 | 3 | 8.00 | 13309.60 | 0.10 | 0.02 | N | O | 1996-01-29 | 1996-03-05 | 1996-01-31 | TAKE BACK RETURN | REG AIR | riously. regular, express dep |
| 1 | 2132 | 4633 | 4 | 28.00 | 28955.64 | 0.09 | 0.06 | N | O | 1996-04-21 | 1996-03-30 | 1996-05-16 | NONE | AIR | lites. fluffily even de |
| 1 | 24027 | 1534 | 5 | 24.00 | 22824.48 | 0.10 | 0.04 | N | O | 1996-03-30 | 1996-03-14 | 1996-04-01 | NONE | FOB | pending foxes. slyly re |
| 1 | 15635 | 638 | 6 | 32.00 | 49620.16 | 0.07 | 0.02 | N | O | 1996-01-30 | 1996-02-07 | 1996-02-03 | DELIVER IN PERSON | MAIL | arefully slyly ex |
| 2 | 106170 | 1191 | 1 | 38.00 | 44694.46 | 0.00 | 0.05 | N | O | 1997-01-28 | 1997-01-14 | 1997-02-02 | TAKE BACK RETURN | RAIL | ven requests. deposits breach a |
| 3 | 4297 | 1798 | 1 | 45.00 | 54058.05 | 0.06 | 0.00 | R | F | 1994-02-02 | 1994-01-04 | 1994-02-23 | NONE | AIR | ongside of the furiously brave acco |
| 3 | 19036 | 6540 | 2 | 49.00 | 46796.47 | 0.10 | 0.00 | R | F | 1993-11-09 | 1993-12-20 | 1993-11-24 | TAKE BACK RETURN | RAIL | unusual accounts. eve |
| 3 | 128449 | 3474 | 3 | 27.00 | 39890.88 | 0.06 | 0.07 | A | F | 1994-01-16 | 1993-11-22 | 1994-01-23 | DELIVER IN PERSON | SHIP | nal foxes wake. |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10 rows in set 性能测试示例
下面我们对外表进行简单的性能测试以本地文件场景和CSS文件场景为例测试环境如下
CPU Intel(R) Xeon(R) CPU E5-2682 v4 2.50GHz DATATPCH 1G 的文本文件文件格式CSV每个表的数据拆成10个文件兼容模式OracleOB版本 4.2 场景1本地文件场景
串行扫描
obclientselect count(*) from LINEITEM;
----------
| COUNT(*) |
----------
| 6001215 |
----------
1 row in set (7.987 sec)
并行扫描
obclientselect /* parallel(10) */ count(*) from LINEITEM;
----------
| COUNT(*) |
----------
| 6001215 |
----------
1 row in set (2.035 sec)
场景2 OSS文件
串行扫描
obclientselect count(*) from LINEITEM;
----------
| COUNT(*) |
----------
| 6001215 |
----------
1 row in set (1 min 24.247 sec)
并行扫描
obclientselect /* parallel(10) */ count(*) from LINEITEM;
----------
| COUNT(*) |
----------
| 6001215 |
----------
1 row in set (8.790 sec)
其他复杂SQL的场景示例
示例1
外表可以像普通表一样与其他表进行链接谓词过滤聚合排序等操作。
外表可以通过parallel hint开启并行查询。
下面例子中customer/orders/lineitem 均为外表。
obclient SELECT * FROM(SELECT /* parallel(10) */l_orderkey,o_orderdate,o_shippriority,sum(l_extendedprice * (1 - l_discount)) AS revenueFROM customer,orders,lineitemWHERE c_mktsegment BUILDINGAND c_custkey o_custkeyAND l_orderkey o_orderkeyAND o_orderdate 1995-03-15AND l_shipdate 1995-03-15GROUP BY l_orderkey,o_orderdate,o_shippriorityORDER BY revenue DESC, o_orderdate)WHERE ROWNUM 10;
--------------------------------------------------------------
| L_ORDERKEY | O_ORDERDATE | O_SHIPPRIORITY | REVENUE |
--------------------------------------------------------------
| 2456423 | 1995-03-05 00:00:00 | 0 | 406181.0111 |
| 3459808 | 1995-03-04 00:00:00 | 0 | 405838.6989 |
| 492164 | 1995-02-19 00:00:00 | 0 | 390324.061 |
| 1188320 | 1995-03-09 00:00:00 | 0 | 384537.9359 |
| 2435712 | 1995-02-26 00:00:00 | 0 | 378673.0558 |
| 4878020 | 1995-03-12 00:00:00 | 0 | 378376.7952 |
| 5521732 | 1995-03-13 00:00:00 | 0 | 375153.9215 |
| 2628192 | 1995-02-22 00:00:00 | 0 | 373133.3094 |
| 993600 | 1995-03-05 00:00:00 | 0 | 371407.4595 |
| 2300070 | 1995-03-13 00:00:00 | 0 | 367371.1452 |
--------------------------------------------------------------
10 rows in set
示例2
外表可以与普通表组合进行查询操作。
下面例子中temp是普通表orders是外表。
obclient SELECT temp.* from temp, orders WHERE temp.c1 orders.O_ORDERDATE and rownum 5;
---------------------
| C1 |
---------------------
| 1995-03-05 00:00:00 |
| 1995-02-22 00:00:00 |
| 1995-02-22 00:00:00 |
| 1995-03-13 00:00:00 |
---------------------
4 rows in set
示例3
外表可以实现将外部数据导入普通表的操作。
下面例子中lineitem_import 为普通表lineitem为外部表通过PDML功能可以将外表lineitem数据并行导入普通表lineitem_import。
INSERT /* enable_parallel_dml parallel(10) */ INTO lineitem_import
SELECT * FROM lineitem;
展望未来
未来外表计划会支持更多的数据源驱动例如aws和cos等支持更丰富的数据格式例如parquet和orc等以及支持压缩格式。目前外表的文件无法进行筛选未来我们还会支持外表的分区通过分区可以进行文件的裁剪提高查询性能。
