通过外表导入OSS数据

操作步骤

本示例将oss_import_test_data.txt中的数据导入AnalyticDB for MySQL的adb_demo库中。

1. 连接目标集群，进入目标数据库。
2. 通过CREATE TABLE，在adb_demo数据库中创建外表。创建CSV、Parquet或TEXT格式OSS外表的建表语法请参见创建OSS外表语法。
3. 查询OSS数据（若仅需导入数据则可跳过此步骤）。

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

   • 对于数据量较大的CSV/TEXT数据文件，强烈建议您按照后续步骤导入AnalyticDB for MySQL后再做查询，否则查询性能可能会较差。
   • 对于Parquet格式数据文件，直接查询的性能一般也比较高，您可以根据需要决定是否进一步导入到AnalyticDB for MySQL后再做查询。

   select uid, other from oss_parquet_external_table where uid < 100 limit 10

4. 通过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)                  

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

     insert into adb_oss_import_test
     select * from oss_import_test_external_table                   

   • 执行INSERT OVERWRITE INTO导入数据。

     insert overwrite into adb_oss_import_test
     select * from oss_import_test_external_table                           

   • 异步执行INSERT OVERWRITE INTO导入数据。

     submit job insert overwrite into adb_oss_import_test
     select * from oss_import_test_external_table ;
     +---------------------------------------+
     | job_id                                |
     +---------------------------------------+
     | 2020112122202917203100908203303000715 |

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

创建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://$bucketname/adb/oss_import_test_data.txt",
    "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通过OSS中ECS的VPC网络（内网）访问OSS。 登录OSS控制台，单击目标Bucket，在Bucket概览页面查看endpoint。
url	OSS中目标数据文件或文件夹的绝对地址。建议文件夹地址以/结尾。 示例：文件：oss://$Bucket名称/adb/oss_import_test_data.txt 文件夹：oss://$Bucket名称/adb_data/
accessid	您在访问OSS中的文件或文件夹时所持有的AccessKey ID。 如何获取您的accessid和accesskey，请参见获取账号的AK信息。
accesskey	您在访问OSS中的文件或文件夹时所持有的Access Key Secret。
delimiter	定义CSV数据文件的列分隔符。
ossnull（可选）	标识NULL值，包含以下四种取值。 默认值为1：EMPTY_SEPARATORS a,"",,c --> "a","",NULL,"c" EMPTY_QUOTES a,"",,c --> "a",NULL,"","c" BOTH a,"",,c --> "a",NULL,NULL,"c" NEITHER a,"",,c --> "a","","","c"
skip_header_line_count（可选）	CSV文件第一行为表头，导入数据时设置为1可自动跳过第一行。或设置为其它值表示开头跳过的行数。默认为0，也就是不跳过。

创建OSS Parquet格式外表

CREATE TABLE IF NOT EXISTS oss_parquet_external_table
(
    uid string,
    other string
)
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
    "url":"oss://****",
    "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名称/adb/oss_import_test_data.txt 文件夹：oss://$Bucket名称/adb_data/
accessid	您在访问OSS中的文件或文件夹时所持有的AccessKey ID。 如何获取您的accessid和accesskey，请参见获取账号的AK信息。
accesskey	您在访问OSS中的文件或文件夹时所持有的Access Key Secret。
format	数据文件的格式，创建Parquet格式文件的外表时必须将其设置为parquet。

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

Parquet基本类型	Parquet的logicalType类型	可对应ADB类型
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

注意

• 外表定义中column的名称应与Parquet文件的中该column的名称必须完全对应（可忽略大小写），而顺序可以是随意的，但建议也保持同样顺序。
• 外表定义中的column可以只选择Parquet文件的部分列，未被外表定义的Parquet文件的列将被忽略；反之如果定义了Parquet文件中未包含的列，该列的查询将均为NULL。

创建OSS TEXT格式外表

CREATE TABLE IF NOT EXISTS oss_text_external_table
(
    uid string,
    other string
) 
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com", 
    "accessid":"LTAIF****5FsE", 
    "accesskey":"Ccw****iWjv", 
    "format":"text", 
    "row_delimiter":"\n",
    "field_delimiter":"\n",
    "URL":"oss://****"
}';

参数	说明
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名称/adb/oss_import_test_data.txt
accessid	您在访问OSS中的文件时所持有的AccessKey ID。 如何获取您的accessid和accesskey，请参见获取账号的AK信息。
accesskey	您在访问OSS中的文件时所持有的Access Key Secret。
format	数据文件的格式，创建TEXT格式文件的外表时必须将其设置为text。
row_delimiter	定义TEXT文件的行分割符，目前仅支持一种：\n
field_delimiter	定义TEXT文件的列分隔符，只能为一个字符。设置为\n表示整行为一个字段。

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

有的OSS数据源是包含分区的，会在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

上述数据中p1为第1级分区，p2为第2级分区，p3为第3级分区。对应这种数据源，一般都希望以分区的模式进行查询，那么就需要在创建OSS外表时额外指明分区列。具体的建表语法示例如下（本例为Parquet格式，分区也支持CSV格式）：

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-xxxx.aliyuncs.com",
  "url":"oss://****/****/oss_parquet_data_dir",
  "accessid":"****",
  "accesskey":"****",
  "format":"parquet",
  "partition_column":"p1, p2, p3"
}'