AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生数据湖分析 （文档停止维护） Serverless Presto SQL 参考 DDL DML INSERT 数据写入Table Store

数据写入Table Store

更新时间：
产品详情
我的收藏

本文以在DLA中，读取OSS数据（customer、nation表），并通过INSERT…SELECT将符合条件的数据写入Table Store为例，介绍如何通过DLA向Table Store中写入数据。

操作步骤

步骤一：创建OSS Schema

  1. CREATE DATABASE tpch_50x_text
  2. WITH DBPROPERTIES (
  3. catalog = 'oss',
  4. location = 'oss://${您的bucket}/datasets/tpch/50x/text_date/'
  5. )

步骤二：创建OSS表

customer表

  1. CREATE EXTERNAL TABLE tpch_50x_text.customer (
  2. c_custkey int,
  3. c_name string,
  4. c_address string,
  5. c_nationkey int,
  6. c_phone string,
  7. c_acctbal double,
  8. c_mktsegment string,
  9. c_comment string
  10. )
  11. ROW FORMAT DELIMITED
  12. FIELDS TERMINATED BY '|'
  13. STORED AS TEXTFILE
  14. LOCATION 'oss://${您的bucket}/datasets/tpch/50x/text_date/customer_text'

nation表

  1. CREATE EXTERNAL TABLE tpch_50x_text.nation (
  2. n_nationkey int,
  3. n_name string,
  4. n_regionkey int,
  5. n_comment string
  6. )
  7. ROW FORMAT DELIMITED
  8. FIELDS TERMINATED BY '|'
  9. STORED AS TEXTFILE
  10. LOCATION 'oss://${您的bucket}/datasets/tpch/50x/text_date/nation_text'

步骤三：创建Table Store Schema

  1. CREATE DATABASE etl_ots_test
  2. WITH DBPROPERTIES (
  3. catalog = 'ots',
  4. location = 'https://${您的instance}.cn-shanghai.ots-internal.aliyuncs.com',
  5. instance = '${您的instance}'
  6. )

步骤四：创建Table Store表test_insert

  1.  CREATE EXTERNAL TABLE test_insert (
  2. id1_int int NOT NULL COMMENT '客户id主键',
  3. c_address varchar(20) NULL COMMENT '客户的地址',
  4. c_acctbal double NULL COMMENT '客户的account balance',
  5. PRIMARY KEY (`id1_int`)
  6. )

步骤五：查询数据

  1. mysql> select * from etl_ots_test.test_insert;
  2. Empty set (0.31 sec)

查询nation表数据，其中CANADA的nationkey是3：

  1. mysql> select n_nationkey, n_name from nation;
  2. +-------------+----------------+
  3. | n_nationkey | n_name         |
  4. +-------------+----------------+
  5. |           0 | ALGERIA        |
  6. |           1 | ARGENTINA      |
  7. |           2 | BRAZIL         |
  8. |           3 | CANADA         |
  9. |           4 | EGYPT          |
  10. |           5 | ETHIOPIA       |
  11. |           6 | FRANCE         |
  12. |           7 | GERMANY        |
  13. |           8 | INDIA          |
  14. |           9 | INDONESIA      |
  15. |          10 | IRAN           |
  16. |          11 | IRAQ           |
  17. |          14 | KENYA          |
  18. |          15 | MOROCCO        |
  19. |          16 | MOZAMBIQUE     |
  20. |          17 | PERU           |
  21. |          18 | CHINA          |
  22. |          19 | ROMANIA        |
  23. |          20 | SAUDI ARABIA   |
  24. |          21 | VIETNAM        |
  25. |          22 | RUSSIA         |
  26. |          23 | UNITED KINGDOM |
  27. |          24 | UNITED STATES  |
  28. +-------------+----------------+
  29. 25 rows in set (0.37 sec)

查询customer表数据，查询条件为nationkey=3以及c_mktsegment=BUILDING的数据：

  1. mysql> select count(*) from customer where c_nationkey = 3 and c_mktsegment = 'BUILDING';
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. |    60350 |
  6. +----------+
  7. 1 row in set (0.66 sec)
  1. mysql> select * from customer where c_nationkey = 3 and c_mktsegment = 'BUILDING' order by c_custkey limit 3;
  2.     +-----------+--------------------+-------------------------+-------------+-----------------+-----------+--------------+----------------------------------------------------------------------------------------------------+
  3. | c_custkey | c_name             | c_address               | c_nationkey | c_phone         | c_acctbal | c_mktsegment | c_comment                                                                                          |
  4. +-----------+--------------------+-------------------------+-------------+-----------------+-----------+--------------+----------------------------------------------------------------------------------------------------+
  5. |        13 | Customer#000000013 | nsXQu0oVjD7PM659uC3SRSp |           3 | 13-761-547-5974 |   3857.34 | BUILDING     | ounts sleep carefully after the close frays. carefully bold notornis use ironic requests. blithely |
  6. |        27 | Customer#000000027 | IS8GIyxpBrLpMT0u7       |           3 | 13-137-193-2709 |   5679.84 | BUILDING     |  about the carefully ironic pinto beans. accoun                                                    |
  7. |        40 | Customer#000000040 | gOnGWAyhSV1ofv          |           3 | 13-652-915-8939 |    1335.3 | BUILDING     | rges impress after the slyly ironic courts. foxes are. blithely                                    |
  8. +-----------+--------------------+-------------------------+-------------+-----------------+-----------+--------------+----------------------------------------------------------------------------------------------------+
  9. 3 rows in set (0.78 sec)

步骤六：导入数据

找到n_name为CANADA、c_mktsegment为BUILDING的客户，然后对c_custkey降序排序，选择前10条数据，将其对应的c_custkey、c_address、c_acctbal三列导入到Table Store的test_insert表中。

先查询数据，确认有几条数据

  1. mysql> select c.c_custkey, c.c_address, c.c_acctbal 
  2. from tpch_50x_text.customer c
  3. join tpch_50x_text.nation n 
  4. on c.c_nationkey = n.n_nationkey
  5. where n.n_name = 'CANADA' 
  6. and c.c_mktsegment = 'BUILDING' 
  7. order by c.c_custkey
  8. limit 10;
  9. +-----------+--------------------------------+-----------+
  10. | c_custkey | c_address                      | c_acctbal |
  11. +-----------+--------------------------------+-----------+
  12. |        13 | nsXQu0oVjD7PM659uC3SRSp        |   3857.34 |
  13. |        27 | IS8GIyxpBrLpMT0u7              |   5679.84 |
  14. |        40 | gOnGWAyhSV1ofv                 |    1335.3 |
  15. |        64 | MbCeGY20kaKK3oalJD,OT          |   -646.64 |
  16. |       255 | I8Wz9sJBZTnEFG08lhcbfTZq3S     |   3196.07 |
  17. |       430 | s2yfPEGGOqHfgkVSs5Rs6 qh,SuVmR |   7905.17 |
  18. |       726 | 4w7DOLtN9Hy,xzZMR              |   6253.81 |
  19. |       905 | f iyVEgCU2lZZPCebx5bGp5        |   -600.73 |
  20. |      1312 | f5zgMB4MHLMSHaX0tDduHAmVd4     |    9459.5 |
  21. |      1358 | t23gsl4TdVXqTZha DioEHIq5w7y   |   5149.23 |
  22. +-----------+--------------------------------+-----------+
  23. 10 rows in set (1.09 sec)

开始导入数据

  1. mysql> insert into etl_ots_test.test_insert (id1_int ,c_address, c_acctbal)
  2.     select c.c_custkey, c.c_address, c.c_acctbal 
  3.     from tpch_50x_text.customer c
  4.     join tpch_50x_text.nation n 
  5.     on c.c_nationkey = n.n_nationkey
  6.     where n.n_name = 'CANADA' 
  7.     and c.c_mktsegment = 'BUILDING' 
  8.     order by c.c_custkey
  9.     limit 10;
  10. +------+
  11. | rows |
  12. +------+
  13. |   10 |
  14. +------+
  15. 1 row in set (2.14 sec)

步骤七：验证结果

  1. mysql> select * from etl_ots_test.test_insert;
  2. +---------+--------------------------------+-----------+
  3. | id1_int | c_address                      | c_acctbal |
  4. +---------+--------------------------------+-----------+
  5. |      13 | nsXQu0oVjD7PM659uC3SRSp        |   3857.34 |
  6. |      27 | IS8GIyxpBrLpMT0u7              |   5679.84 |
  7. |      40 | gOnGWAyhSV1ofv                 |    1335.3 |
  8. |      64 | MbCeGY20kaKK3oalJD,OT          |   -646.64 |
  9. |     255 | I8Wz9sJBZTnEFG08lhcbfTZq3S     |   3196.07 |
  10. |     430 | s2yfPEGGOqHfgkVSs5Rs6 qh,SuVmR |   7905.17 |
  11. |     726 | 4w7DOLtN9Hy,xzZMR              |   6253.81 |
  12. |     905 | f iyVEgCU2lZZPCebx5bGp5        |   -600.73 |
  13. |    1312 | f5zgMB4MHLMSHaX0tDduHAmVd4     |    9459.5 |
  14. |    1358 | t23gsl4TdVXqTZha DioEHIq5w7y   |   5149.23 |
  15. +---------+--------------------------------+-----------+
  16. 10 rows in set (0.27 sec)
上一篇：数据写入RDS下一篇：SELECT
文档推荐