全部产品
Search

搜索本产品

    云原生数据湖分析 DLA - 文档停止维护：数据写入Table Store

    文档中心

    云原生数据湖分析 DLA - 文档停止维护：数据写入Table Store

    更新时间：May 24, 2019

    本文以在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)