Use external tables to import data from Apsara File Storage for HDFS to AnalyticDB for MySQL

Prerequisites

• The version of the AnalyticDB for MySQL cluster is V3.1.4.4 or later.
  Note

  • For information about how to view the version of an AnalyticDB for MySQL cluster, see How can I view the version of an AnalyticDB for MySQL cluster?
  • To upgrade the version of an AnalyticDB for MySQL cluster, Submit a ticket.
• The Apsara File Storage for HDFS data file is in the CSV, Parquet, or ORC format.
• An Apsara File Storage for HDFS cluster is created, and the data that you want to import is stored in an Apsara File Storage for HDFS folder. In this topic, the data is stored in the hdfs_import_test_data.csv folder.
• The following service access ports for the AnalyticDB MySQL cluster are configured in the Apsara File Storage for HDFS cluster:
  • namenode: used to read and write metadata of a file system. You can configure the port number by using the fs.defaultFS parameter. The default port number is 8020.

    For information about the detailed configurations, see core-default.xml.

  • datanode: used to read and write data. You can configure the port number by using the dfs.datanode.address parameter. The default port number is 50010.

    For information about the detailed configurations, see hdfs-default.xml.

• If the AnalyticDB MySQL cluster is in elastic mode, you must turn on ENI in the Network Information section of the Cluster Information page.

Procedure

1. Connect to the AnalyticDB MySQL cluster. For more information, see Connect to an AnalyticDB for MySQL cluster.
2. Create a destination database. For more information, see Create a database.

   In this example, the database named adb_demo is used as the destination database in the AnalyticDB MySQL cluster.

3. Create an external mapping table in the CSV, Parquet, or ORC format in the adb_demo destination database by using the CREATE TABLE statement.
   • To create a standard external mapping table, see Create an Apsara File Storage for HDFS external table.
   • To create a partitioned external mapping table, see Create a partitioned external table for Apsara File Storage for HDFS.
4. Create a destination table.

   You can create a destination table in the adb_demo destination database by using the following statements to store data imported from Apsara File Storage for HDFS.

   • The following statement shows how to create a destination table corresponding to the standard external mapping table. In this example, the destination table is named adb_hdfs_import_test.

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

   • The following statement shows how to create a destination table corresponding to the partitioned external mapping table. In this example, the destination table is named adb_hdfs_import_parquet_partition. To create the table, you must define ordinary columns (such as uid and other) and partition columns (such as p1, p2, and p3) in the statement.

     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 Apsara File Storage for HDFS to the destination AnalyticDB MySQL cluster.

   You can use one of the following methods to import data. The syntax to import data by using a partitioned table is the same as the syntax to import data by using a standard table.

   • Method 1 (recommended): Use the INSERT OVERWRITE INTO statement to import data. This method allows you to batch import data and provides good performance. If the import succeeds, the data is available for query. Otherwise, the data is rolled back to its previous state. The following statement shows how to import data by using this method:

     INSERT OVERWRITE INTO adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

   • Method 2: Use the INSERT INTO statement to import data. This method allows you to query imported data in real time. This method is suitable when you want to import a small amount of data. The following statement shows how to import data by using this method:

     INSERT INTO adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

   • Method 3: Submit an asynchronous task to import data. The following statement shows how to import data by using this method:

     SUBMIT JOB INSERT OVERWRITE INTO adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

     The following result is returned:

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

     You can check the state of the asynchronous task based on the job_id value. For more information, see Asynchronously submit an import or export task.

What to do next

SELECT * FROM adb_hdfs_import_test LIMIT 100;

Create an Apsara File Storage for HDFS external table

• Create an external table in the CSV format
  The following statement shows how to create an external table in the CSV format:

  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	The storage engine used for the external table. In this example, HDFS is used as the storage engine.
  TABLE_PROPERTIES		The connection information used by AnalyticDB MySQL to access Apsara File Storage for HDFS data.
  format		The format of the data file. When you create an external table in the CSV format, you must set this parameter to csv.
  delimiter		The column delimiter of the CSV data file. In this example, a comma (,) is used as the delimiter.
  hdfs_url		The absolute URL of the destination data file or folder in the Apsara File Storage for HDFS cluster. The URL 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 column of the table. Separate multiple columns with commas (,). For more information about the methods to define a partition column, see Create a partitioned external table for Apsara File Storage for HDFS.
  compress_type		The compression type of the data file. CSV files support only the gzip compression type.
  skip_header_line_count		The number of header rows to skip when you import data. The first row of a CSV file is the table header. If you set this parameter to 1, the first row of the file is skipped when you import data. The default value of this parameter is 0, which indicates that no rows are skipped.

• Create an external table in the Parquet or ORC format
  The following statement shows how to create an external table in the Parquet format:

  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	The storage engine of the external table. In this example, HDFS is used as the storage engine.
  TABLE_PROPERTIES		The connection information used by AnalyticDB MySQL to access Apsara File Storage for HDFS data.
  format		The format of the data file. When you create an external table in the Parquet format, you must set this parameter to parquet. When you create an external table in the ORC format, you must set this parameter to orc.
  hdfs_url		The absolute URL of the destination data file or folder in the Apsara File Storage for HDFS cluster. The URL must start with hdfs://.
  partition_column	No	The partition column of the table. Separate multiple columns with commas (,). For more information about the methods for defining a partition column, see Create a partitioned external table for Apsara File Storage for HDFS.

  Note

  • The column names used in the statement to create an external table must be the same as those in the Parquet or ORC file. Column names are not case sensitive. The sequence of the columns in the statement must be the same as that in the Parquet file.
  • When you create an external table, you can choose only specific columns in a Parquet or ORC file as the columns of the external table. Columns that are not selected in the Parquet or ORC file are not imported.
  • If the statement used to create an external table contains a column that is not in the Parquet or ORC file, NULL is returned in the query results of this column.
  The following table describes the mappings between data types in Parquet and AnalyticDB for MySQL V3.0.

  Basic type in Parquet	Logical type in Parquet	Data type in AnalyticDB for MySQL V3.0
  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 (JSON is available if a Parquet file contains a column in the JSON format.)
  INT32	DATE	DATE
  INT64	TIMESTAMP_MILLIS	TIMESTAMP or DATETIME
  INT96	None	TIMESTAMP or DATETIME

  Notice External tables in the Parquet format do not support the STRUCT data type. Tables cannot be created for this data type.
  The following table describes the mappings between data types in ORC and AnalyticDB for MySQL V3.0.

  Data type in ORC	Data type in AnalyticDB for MySQL V3.0
  BOOLEAN	BOOLEAN
  BYTE	TINYINT
  SHORT	SMALLINT
  INT	INT or INTEGER
  LONG	BIGINT
  DECIMAL	DECIMAL
  FLOAT	FLOAT
  DOUBLE	DOUBLE
  BINARY STRING VARCHAR	VARCHAR STRING JSON (JSON is available if an ORC file contains a column in the JSON format.)
  TIMESTAMP	TIMESTAMP or DATETIME
  DATE	DATE

  Notice External tables in the ORC format do not support the LIST, STRUCT, or UNION data type. Tables that contain columns in the MAP data type can be created, but queries cannot be performed.

Create a partitioned external table for Apsara File Storage for HDFS

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 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",  //Specify the format of the file. To create a CSV or ORC file, change the format value to csv or orc. 
  "partition_column":"p1, p2, p3"  //If you want to query the Apsara File Storage for HDFS data by partition, you must specify partition columns (partition_column) in the statement used to create an external table when you import data into AnalyticDB MySQL. 
}';