AnalyticDB:Import data using external tables

Prerequisites

• Your AnalyticDB for MySQL cluster must run kernel version 3.1.4 or later. To create an external table for data in external cloud storage, such as AWS S3, Azure Blob Storage, or Google Cloud Storage, the kernel version must be 3.2.6 or later.

  Note

  To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.

• The HDFS data files must be in CSV, Parquet, or ORC format.

• An HDFS cluster is available, and the data to be imported is stored in an HDFS file. This topic uses the hdfs_import_test_data.csv file as an example.

• The following service access ports must be configured in the HDFS cluster for your AnalyticDB for MySQL cluster:

  • namenode: Reads and writes file system metadata. The port number is configured in the fs.defaultFS parameter. The default port is 8020.

    For configuration details, see core-default.xml.

  • datanode: Reads and writes data. The port number is configured in the dfs.datanode.address parameter. The default port is 50010.

    For configuration details, see hdfs-default.xml.

• An elastic network interface (ENI) must be enabled for your AnalyticDB for MySQL cluster that runs in Data Warehouse Edition in elastic mode.

  Important

  • Log on to the AnalyticDB for MySQL console. On the Cluster Information page, in the Network Information section, turn on the ENI network switch.

  • Enabling or disabling the ENI network interrupts the database connection for approximately 2 minutes, during which read and write operations are unavailable. Carefully evaluate the potential impact before you enable or disable the ENI network.

Procedure

1. (Optional) Configure public network access.

   If you need to use an external table to connect to object storage services from other cloud providers, such as AWS S3, Azure Blob Storage, or Google Cloud Storage, ensure that your AnalyticDB for MySQL cluster can access the public network.

   • Configure a NAT gateway and an Elastic IP Address (EIP) for your AnalyticDB for MySQL cluster's VPC.

     1. Create a NAT gateway.

        The NAT gateway must be in the same region as your AnalyticDB for MySQL instance.

     2. Associate an Elastic IP Address (EIP).

     3. Create an SNAT entry.

        We recommend that you create an SNAT entry at the vSwitch level. You can specify any vSwitch.

   • Enable elastic network interface (ENI) access for your AnalyticDB for MySQL cluster.

     Important

     • Log on to the AnalyticDB for MySQL console. On the Cluster Information page, in the Network Information section, turn on the ENI switch.

     • Enabling or disabling the ENI interrupts the database connection for approximately 2 minutes. During this period, read and write operations are unavailable. Carefully evaluate the potential impact before you enable or disable the ENI.

2. Create a destination database. In this example, the destination database in the AnalyticDB for MySQL cluster is named adb_demo.

   CREATE DATABASE IF NOT EXISTS adb_demo; 

3. Run the CREATE TABLE statement in the adb_demo destination database to create an external table in CSV, Parquet, or ORC format.

   • If you use an HDFS data source:

     • Create an HDFS external table.

     • Create a partitioned HDFS external table.

   • To connect to an object storage service from another cloud provider, such as AWS S3, Azure Blob Storage, or Google Cloud Storage, create an external table for cloud storage.

4. Create a destination table.

   Use one of the following statements to create a destination table in the adb_demo database to store data imported from HDFS:

   • Create a destination table for a standard external table. In this example, the destination table is named adb_hdfs_import_test. The syntax is as follows:

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

   • When you create a destination table for a partitioned external table, you must define both standard columns, such as uid and other, and partition key columns, such as p1, p2, and p3, in the statement. In this example, the destination table is named adb_hdfs_import_parquet_partition. The syntax is as follows:

     CREATE TABLE IF NOT EXISTS adb_hdfs_import_parquet_partition
     (
         uid string,
         other string,
         p1 date,
         p2 int,
         p3 varchar
     )
     DISTRIBUTED BY HASH(uid);

5. Import data from HDFS into the destination AnalyticDB for MySQL cluster.

   Choose an import method based on your business requirements. The import syntax is the same for both partitioned and standard tables. The following examples use a standard table:

   • (Recommended) Method 1: Use INSERT OVERWRITE to import data. This method supports batch import and delivers high performance. Data becomes visible after a successful import. If the import fails, the data is rolled back. The following code provides an example:

     INSERT OVERWRITE adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

   • Method 2: Use INSERT INTO to import data. You can query the inserted data in real time. Use this method for small amounts of data. The following code provides an example:

     INSERT INTO adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

   • Method 3: Run an asynchronous task to import data. The following code provides an example:

     SUBMIT JOB INSERT OVERWRITE adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

     The following result is returned:

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

     You can also check the status of the asynchronous task by using the returned job_id. For more information, see Asynchronously submit an import task.

Next steps

SELECT * FROM adb_hdfs_import_test LIMIT 100;

Create an HDFS external table

• Create an external table for a CSV file

  Use the following statement:

  CREATE TABLE IF NOT EXISTS hdfs_import_test_external_table
  (
      uid string,
      other string
  )
  ENGINE='HDFS'
  TABLE_PROPERTIES='{
      "format":"csv",
      "delimiter":",",
      "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_import_test_csv_data/hdfs_import_test_data.csv"
  }';

  Parameter	Required	Description
  ENGINE='HDFS'	Yes	Specifies the storage engine. Must be set to HDFS for HDFS external tables.
  TABLE_PROPERTIES		Defines properties for AnalyticDB for MySQL to access HDFS data.
  format		The format of the data file. Set to csv for CSV files.
  delimiter		The column delimiter in the CSV data file. This example uses a comma (,).
  hdfs_url		The absolute address of the target data file or folder in the HDFS cluster. The address must start with hdfs://. Example: hdfs://172.17.***.***:9000/adb/hdfs_import_test_csv_data/hdfs_import_test_data.csv
  partition_column	No	The partition key columns of the external table. Separate multiple columns with commas (,). For more information, see Create a partitioned HDFS external table.
  compress_type		The compression type of the data file. For CSV files, only the Gzip type is supported.
  skip_header_line_count		The number of header lines to skip from the start of the file. To skip a single-line table header, set this parameter to 1. Default value: 0. This means no rows are skipped.
  hdfs_ha_host_port		If high availability (HA) is configured for the HDFS cluster, you must specify the hdfs_ha_host_port parameter when you create an external table. The format is ip1:port1,ip2:port2. The IP addresses and ports correspond to the active and standby namenode instances. Example: 192.168.xx.xx:8020,192.168.xx.xx:8021

• Create an HDFS external table for a Parquet or ORC file

  The following example shows how to create an HDFS external table for a Parquet file:

  CREATE TABLE IF NOT EXISTS hdfs_import_test_external_table
  (
      uid string,
      other string
  )
  ENGINE='HDFS'
  TABLE_PROPERTIES='{
      "format":"parquet",
      "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_import_test_parquet_data/"
  }';

  Parameter	Required	Description
  ENGINE='HDFS'	Yes	Specifies the storage engine. Must be set to HDFS for HDFS external tables.
  TABLE_PROPERTIES		Defines properties for AnalyticDB for MySQL to access HDFS data.
  format		The format of the data file. To create an external table for a Parquet file, set this parameter to parquet. To create an external table for an ORC file, set this parameter to orc.
  hdfs_url		The absolute address of the target data file or folder in the HDFS cluster. The address must start with hdfs://.
  partition_column	No	The partition key columns of the external table. Separate multiple columns with commas (,). For more information, see Create a partitioned HDFS external table.
  hdfs_ha_host_port		If high availability (HA) is configured for the HDFS cluster, you must specify the hdfs_ha_host_port parameter when you create an external table. The format is ip1:port1,ip2:port2. The IP addresses and ports correspond to the active and standby namenode instances. Example: 192.168.xx.xx:8020,192.168.xx.xx:8021

  Note

  • The column names and their order in the CREATE EXTERNAL TABLE statement must match those in the source Parquet or ORC file. Column names are case-insensitive.

  • You can create an external table using a subset of columns from the source file. Columns not specified in the CREATE EXTERNAL TABLE statement are ignored.

  • If the CREATE EXTERNAL TABLE statement includes a column that does not exist in the Parquet or ORC file, queries on that column return NULL.

  Data type mappings between Parquet files and AnalyticDB for MySQL

  Parquet primitive data type	Parquet logicalType	Data type in AnalyticDB for MySQL
  BOOLEAN	None	BOOLEAN
  INT32	INT_8	TINYINT
  INT32	INT_16	SMALLINT
  INT32	None	INT or INTEGER
  INT64	None	BIGINT
  FLOAT	None	FLOAT
  DOUBLE	None	DOUBLE
  FIXED_LEN_BYTE_ARRAY BINARY INT64 INT32	DECIMAL	DECIMAL
  BINARY	UTF-8	VARCHAR STRING JSON (if the Parquet column is known to be in JSON format)
  INT32	DATE	DATE
  INT64	TIMESTAMP_MILLIS	TIMESTAMP or DATETIME
  INT96	None	TIMESTAMP or DATETIME

  Important

  External tables for Parquet files do not support the STRUCT type. If you use this type, the table creation fails.

  Data type mappings between ORC files and AnalyticDB for MySQL

  Data type in ORC files	Data type in AnalyticDB for MySQL
  BOOLEAN	BOOLEAN
  BYTE	TINYINT
  SHORT	SMALLINT
  INT	INT or INTEGER
  LONG	BIGINT
  DECIMAL	DECIMAL
  FLOAT	FLOAT
  DOUBLE	DOUBLE
  BINARY STRING VARCHAR	VARCHAR STRING JSON (if the ORC column is known to be in JSON format)
  TIMESTAMP	TIMESTAMP or DATETIME
  DATE	DATE

  Important

  External tables for ORC files do not support complex types such as LIST, STRUCT, or UNION. If you use these types, the table creation fails. You can create an external table for an ORC file if a column uses the MAP type, but queries on that table will fail.

Create a partitioned HDFS external table

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
        ├── p3=HANGZHOU
        │ └── 000000_0
        └── p3=SHENZHEN
            └── 000000_0

CREATE TABLE IF NOT EXISTS hdfs_parquet_partition_table
(
  uid varchar,
  other varchar,
  p1 date,
  p2 int,
  p3 varchar
)
ENGINE='HDFS'
TABLE_PROPERTIES='{
  "hdfs_url":"hdfs://172.17.***.**:9000/adb/parquet_partition_classic/",
  "format":"parquet", //To create an external table for a CSV or ORC file, change the value of format to csv or orc.
  "partition_column":"p1, p2, p3" // To query partitioned HDFS data by partition, specify the partition_column parameter in the CREATE TABLE statement when importing data to AnalyticDB for MySQL.
}';

Create an external table for cloud storage

{
   "Version": "2012-10-17",
   "Statement": [
     {
       "Effect": "Allow",
       "Action": [
         "s3:Get*",
         "s3:List*",
         "s3:Describe*",
         "s3-object-lambda:Get*",
         "s3-object-lambda:List*"
       ],
       "Resource": "*"
     }
   ]
}

{
   "Version": "2012-10-17",
   "Statement": [
     {
       "Effect": "Allow",
       "Action": [
         "s3:",
         "s3-object-lambda:"
       ],
       "Resource": "*"
     }
   ]
}

CREATE TABLE t2(c1 int, c2 int, p1 int)
ENGINE='hdfs'
TABLE_PROPERTIES='{
  "partition_column":"p1",
  "format" : "parquet",
  "hdfs_url" : "gs://adbtest2/tbls/table1",
  "gcs.project_id":"test-project",
  "gcs.client_email":"adbtest@test-project.iam.gserviceaccount.com",
  "gcs.token_uri":"https://oauth2.googleapis.cn/token",
  "gcs.private_key_id":"xxxx",
  "gcs.private_key":"-----BEGIN PRIVATE KEY-----\nMIIEvgIBADANBgkqhkiG9w0BAQEFA****-----END PRIVATE KEY-----\n"
}'