导入数据并查询

操作步骤

1. 通过CREATE TABLE，在adb_demo数据库中创建外表。创建CSV、Parquet或TEXT格式OSS外表的建表语法请参见创建OSS外表语法。

2. 查询OSS数据。

   查询外表映射表和查询AnalyticDB for MySQL内表语法没有区别，您可以方便地直接进行查询，如本步骤的示例代码所示。

   select uid, other from oss_import_test_external_table where uid < 100 limit 10;

   • 对于数据量较大的CSV或TEXT数据文件，强烈建议您按照后续步骤导入AnalyticDB for MySQL后再做查询，否则查询性能可能会较差。

   • 对于Parquet格式数据文件，直接查询的性能一般也比较高，您可以根据需要决定是否进一步导入到AnalyticDB for MySQL后再做查询。

3. 通过CREATE TABLE，在adb_demo数据库中创建目标表adb_oss_import_test存储从OSS中导入的数据。

   CREATE TABLE IF NOT EXISTS adb_oss_import_test
   (
       uid string,
       other string
   )
   DISTRIBUTED BY HASH(uid);                  

4. 执行INSERT语句将OSS数据导入AnalyticDB for MySQL。

   重要

   使用INSERT INTO或INSERT OVERWRITE SELECT导入数据时，默认是同步执行流程。如果数据量较大，达到几百GB，客户端到AnalyticDB for MySQL服务端的连接会中断，导致数据导入失败。因此，如果您的数据量较大时，推荐使用SUBMIT JOB INSERT OVERWRITE SELECT异步执行导入。

   • 方式一：执行INSERT INTO导入数据，当主键重复时会自动忽略当前写入数据，不进行更新覆盖，作用等同于INSERT IGNORE INTO，详情请参见INSERT INTO。示例如下：

     INSERT INTO adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • 方式二：执行INSERT OVERWRITE导入数据，会覆盖表中原有的数据。示例如下：

     INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • 方式三：异步执行INSERT OVERWRITE导入数据。 通常使用SUBMIT JOB提交异步任务，由后台调度，可以在写入任务前增加Hint（/*+ direct_batch_load=true*/）加速写入任务。详情请参见异步写入。示例如下：

     SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

     返回结果如下：

     +---------------------------------------+
     | job_id                                |
     +---------------------------------------+
     | 2020112122202917203100908203303****** |

     关于异步提交任务详情，请参见异步提交导入任务。

5. 执行以下命令，查询adb_oss_import_test表的数据。

   SELECT * FROM adb_oss_import_test;

创建OSS外表语法

• 创建OSS CSV格式外表

  • 示例的oss_import_test_data.txt文件为CSV格式，本节介绍CSV格式的OSS外表创建语法。

    CREATE TABLE IF NOT EXISTS oss_import_test_external_table
    (
        uid string,
        other string
    )
    ENGINE='OSS'
    TABLE_PROPERTIES='{
        "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
        "url":"oss://<bucket-name>/adb_data/",
        "accessid":"LTAIF****5FsE",
        "accesskey":"Ccw****iWjv",
        "delimiter":";",
        "skip_header_line_count":1,
    }'; 

    参数	是否必填	说明
    ENGINE='OSS'	必填	表示该表是外部表，使用的存储引擎是OSS。
    TABLE_PROPERTIES		用于告知AnalyticDB for MySQL如何访问OSS中的数据。
    endpoint		OSS的EndPoint（地域节点）。 说明 目前仅支持AnalyticDB for MySQL通过ECS的VPC网络访问OSS。 登录OSS控制台，单击目标Bucket，在Bucket概览页面查看EndPoint（地域节点）。
    url		OSS中源数据文件或文件夹的绝对路径。文件夹绝对路径以正斜线（/）结尾。 路径末尾支持通配符*，用于匹配该路径下所有符合该模式的文件或文件夹。 示例： 文件：oss://<bucket-name>/adb/oss_import_test_data.csv。 文件夹：oss://<bucket-name>/adb_data/。 说明 若指定为源数据文件夹的路径，成功创建外表后，外表中的数据为该文件夹下的所有数据。 * 模糊查询：oss://<bucket-name>/adb_data/list_file_with_prefix/test*。 说明 该模糊查询示例将匹配到满足前缀条件的所有文件和文件夹，例如：oss://<bucket-name>/adb_data/list_file_with_prefix/testfile1和 oss://<bucket-name>/adb_data/list_file_with_prefix/test1/file2。
    accessid		您在访问OSS中的文件或文件夹时所持有的AccessKey ID。 如何获取您的AccessKey ID和AccessKey Secret，请参见账号与权限。
    accesskey		您在访问OSS中的文件或文件夹时所持有的AccessKey Secret。
    delimiter		定义CSV数据文件的列分隔符。例如您可以将列分隔符设置为英文逗号（,）。
    null_value	选填	定义CSV数据文件的NULL值。默认将空值定义为NULL，即"null_value": ""。 说明 AnalyticDB for MySQL集群需为V3.1.4.2或以上版本才支持配置该参数。关于版本信息，请参见新功能发布记录。
    ossnull		选择CSV数据文件中NULL值的对应规则。取值范围如下： 1（默认值）：表示EMPTY_SEPARATORS，即仅将空值定义为NULL 。 示例：a,"",,c --> "a","",NULL,"c" 2：表示EMPTY_QUOTES，即仅将""定义为NULL。 示例：a,"",,c --> "a",NULL,"","c" 3：表示BOTH，即同时将空值和""定义为NULL。 示例：a,"",,c --> "a",NULL,NULL,"c" 4：表示NEITHER，即空值和""均不定义为NULL。 示例：a,"",,c --> "a","","","c" 说明 上述各示例的前提为"null_value": ""。
    skip_header_line_count		定义导入数据时需要在开头跳过的行数。CSV文件第一行为表头，若设置该参数为1，导入数据时可自动跳过第一行的表头。 默认取值为0，即不跳过。
    oss_ignore_quote_and_escape		是否忽略字段值中的引号和转义。默认取值为false，即不忽略字段值中的引号和转义。 说明 AnalyticDB for MySQL集群需为V3.1.4.2或以上版本才支持设置该参数。关于版本信息，请参见新功能发布记录。

• 创建OSS Parquet格式/OSS ORC格式外表

  • 以Parquet格式为例，创建OSS外表的语句如下：

    CREATE TABLE IF NOT EXISTS oss_import_test_external_table
    (
        uid string,
        other string
    )
    ENGINE='OSS'
    TABLE_PROPERTIES='{
        "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
        "url":"oss://<bucket-name>/adb_data/",
        "accessid":"LTAIF****5FsE",
        "accesskey":"Ccw****iWjv",
        "format":"parquet"
    }';

    参数	说明
    ENGINE= 'OSS'	表示该表是外部表，使用的存储引擎是OSS。
    TABLE_PROPERTIES	用于告知AnalyticDB for MySQL如何访问OSS中的数据。
    endpoint	OSS的EndPoint（地域节点）（域名节点）。 说明 目前仅支持AnalyticDB for MySQL通过OSS中ECS的VPC网络（内网）访问OSS。 登录OSS控制台，单击目标Bucket，在Bucket概览页面查看EndPoint（地域节点）。
    url	OSS中源数据文件或文件夹的绝对路径。建议文件夹绝对路径以正斜线（/）结尾。 示例： 文件：oss://<bucket-name>/adb/oss_import_test_data.parquet。 文件夹：oss://<bucket-name>/adb_data/。 说明 建表时请将示例中的url替换为实际的OSS路径。 若指定为源数据文件夹的路径，成功创建外表后，外表中的数据为该文件夹下的所有数据。
    accessid	您在访问OSS中的文件或文件夹时所持有的AccessKey ID。 如何获取您的AccessKey ID和AccessKey Secret，请参见账号与权限。
    accesskey	您在访问OSS中的文件或文件夹时所持有的AccessKey Secret。
    format	数据文件的格式。 创建Parquet格式文件的外表时需设置为parquet。 创建ORC格式文件的外表时需设置为orc。 说明 不指定format时，默认格式为CSV。

    说明

    • 外表创建语句中的列名需与Parquet或ORC文件中该列的名称完全相同（可忽略大小写），且列的顺序需要一致。

    • 创建外表时，可以仅选择Parquet或ORC文件中的部分列作为外表中的列，未被选择的列不会被导入。

    • 如果创建外表创建语句中出现了Parquet或ORC文件中不存在的列，针对该列的查询结果均会返回NULL。

    创建Parquet格式文件的外表时，需要注意数据类型的对应关系，具体规则如下：

    Parquet文件与AnalyticDB for MySQL的数据类型映射关系如下表。

    Parquet基本类型	Parquet的logicalType类型	AnalyticDB for MySQL的数据类型
    BOOLEAN	无	BOOLEAN
    INT32	INT_8	TINYINT
    INT32	INT_16	SMALLINT
    INT32	无	INT或INTEGER
    INT64	无	BIGINT
    FLOAT	无	FLOAT
    DOUBLE	无	DOUBLE
    FIXED_LEN_BYTE_ARRAY BINARY INT64 INT32	DECIMAL	DECIMAL
    BINARY	UTF-8	VARCHAR STRING JSON（如果已知Parquet该列内容为JSON格式）
    INT32	DATE	DATE
    INT64	TIMESTAMP_MILLIS	TIMESTAMP或DATETIME
    INT96	无	TIMESTAMP或DATETIME

    重要

    Parquet格式外表暂不支持STRUCT类型，会导致建表失败。

针对带有分区的Parquet或CSV数据文件创建OSS外表

parquet_partition_classic/
├── p1=2020-01-01
│ ├── p2=4
│ │ ├── p3=SHANGHAI
│ │ │ ├── 000000_0
│ │ │ └── 000000_1
│ │ └── p3=SHENZHEN
│ │     └── 000000_0
│ └── p2=6
│     └── p3=SHENZHEN
│         └── 000000_0
├── p1=2020-01-02
│ └── p2=8
│     ├── p3=SHANGHAI
│     │ └── 000000_0
│     └── p3=SHENZHEN
│         └── 000000_0
└── p1=2020-01-03
    └── p2=6
        ├── p2=HANGZHOU
        └── p3=SHENZHEN
            └── 000000_0

CREATE TABLE IF NOT EXISTS oss_parquet_partition_table
(
  uid varchar,
  other varchar,
  p1 date,
  p2 int,
  p3 varchar
)
ENGINE='OSS'
TABLE_PROPERTIES='{
  "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
  "url":"oss://<bucket-name>/adb/oss_parquet_data_dir",
  "accessid":"LTAIF****5FsE",
  "accesskey":"Ccw****iWjv",
  "format":"parquet",
  "partition_column":"p1, p2, p3"
}';

相关文档

更多导入数据方式，请参见支持的数据源。