AnalyticDB:Use external tables to import HDFS data

Prerequisites

Before you begin, confirm the following:

Cluster requirements:

• Your AnalyticDB for MySQL cluster runs kernel version 3.1.4 or later. To check and update the version, log on to the AnalyticDB for MySQL console and go to the Configuration Information section on the Cluster Information page. For instructions, see Update the minor version of a cluster.

• Your cluster is a Data Warehouse Edition cluster running in elastic mode. Elastic mode is required for Elastic Network Interface (ENI) access.

HDFS requirements:

• Your HDFS data files are in CSV, Parquet, or ORC format.

• Your HDFS cluster is running and the data to import is available in an HDFS directory. This topic uses hdfs_import_test_data.csv as an example.

• The following service ports are open in the HDFS cluster for AnalyticDB for MySQL:

  • NameNode: Manages file system metadata. Default port: 8020, configured via fs.defaultFS. See core-default.xml.

  • DataNode: Handles data reads and writes. Default port: 50010, configured via dfs.datanode.address. See hdfs-default.xml.

Network configuration:

Enable ENI access on your cluster before importing data.

1. Log on to the AnalyticDB for MySQL console.

2. On the Cluster Information page, go to the Network Information section.

3. Turn on the Elastic Network Interface (ENI) switch.

Import HDFS data

CREATE DATABASE IF NOT EXISTS adb_demo;

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"
}';

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/"
}';

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

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

INSERT OVERWRITE adb_hdfs_import_test
SELECT * FROM hdfs_import_test_external_table;

SUBMIT JOB INSERT OVERWRITE adb_hdfs_import_test
SELECT * FROM hdfs_import_test_external_table;

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

SELECT * FROM adb_hdfs_import_test LIMIT 100;

Create a partitioned HDFS external table

Use partitioned external tables when your HDFS data is organized into a hierarchical directory structure. HDFS supports partitioned data in Parquet, CSV, and ORC formats. Partitions form a directory hierarchy like this:

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

The following statement creates an external table for this partitioned Parquet dataset:

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",
  "partition_column":"p1, p2, p3"
}';

To use CSV or ORC data instead, change "format" to "csv" or "orc". If format is omitted, CSV is used by default.

Rules for partitioned external tables:

• The partition_column property lists the partition key columns in the order they appear in the directory hierarchy. The order must match exactly.

• Define partition key columns in the CREATE TABLE statement and place them at the end of the column list, after all data columns.

• Partition key columns support these data types: BOOLEAN, TINYINT, SMALLINT, INT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL, VARCHAR, STRING, DATE, and TIMESTAMP.

• Partition key columns behave like regular columns in queries — you can SELECT and filter on them.

• For other TABLE_PROPERTIES parameters, see the CSV parameters table or Parquet/ORC parameters table.

External table parameters

Limitations

• CSV external tables support Gzip compression only.

• Parquet external tables cannot use columns of the STRUCT type.

• ORC external tables cannot use the LIST, STRUCT, or UNION type. The MAP type can be used in table creation but cannot be queried.

Data type mappings

What's next

• Asynchronously submit an import task — monitor long-running imports using a job_id

• Update the minor version of a cluster — keep your cluster on a supported kernel version