MaxCompute:Query Parquet data with Schemaless Query

Prerequisites

• You have an EMR Serverless Spark workspace.

  This topic uses the China (Hangzhou) region as an example. Assume that the workspace is named schemaless_test and the OSS bucket is named oss-mc-test.

• You have a MaxCompute project.

Step 1: Generate Parquet data using Serverless Spark

1. Log on to the E-MapReduce console. In the left-side navigation pane, choose EMR Serverless > Spark.

2. On the Spark page, click the name of your workspace. On the EMR Serverless Spark page, click Data Development in the left-side navigation pane.

3. Create a Spark SQL job, enter the following SQL commands to create a Parquet table and populate it with data, and then click Run.

   Important

   Before you run the following commands, ensure that the selected data directory and database in the upper-right corner of the page are associated with your OSS bucket path.

   CREATE TABLE example_table01 (
       id INT,
       name STRING,
       age INT
   ) USING PARQUET;
   INSERT INTO example_table01 VALUES
   (1, 'Alice', 30),
   (2, 'Bob', 25),
   (3, 'Charlie', 35),
   (4, 'David', 40),
   (5, 'Eve', 32),
   (6, 'Frank', 28),
   (7, 'Grace', 33),
   (8, 'Hannah', 29),
   (9, 'Ian', 36),
   (10, 'Julia', 31);
   SELECT * FROM example_table01;

4. After the run is successful, you can view the generated example_table01 table on the Metadata page in the Data Lake Formation console. On the Metadata page, select the Tables tab to view the created example_table01 table. The table is in Parquet format.

   You can also view the Parquet files in your OSS bucket. In the example_table01/ directory, you will find a _SUCCESS marker file and two .snappy.parquet data files. This indicates that the Parquet data was successfully written.

Step 2: Read data using Schemaless Query

To learn more about reading Parquet files in MaxCompute, see Schemaless Query.

When Spark writes data to a Parquet table, it generates a marker file named _SUCCESS. You must use the file_pattern_blacklist parameter to add the _SUCCESS file to the blacklist. This prevents MaxCompute from reading it as a data file, which would cause an error.

SELECT * FROM 
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/example_table01/' 
(
  'file_format'='parquet',
  'file_pattern_blacklist'='.*_SUCCESS.*'
);

The following result is returned:

+------------+------------+------------+
| id         | name       | age        |
+------------+------------+------------+
| 1          | Alice      | 30         |
| 2          | Bob        | 25         |
| 3          | Charlie    | 35         |
| 4          | David      | 40         |
| 5          | Eve        | 32         |
| 6          | Frank      | 28         |
| 7          | Grace      | 33         |
| 8          | Hannah     | 29         |
| 9          | Ian        | 36         |
| 10         | Julia      | 31         |
+------------+------------+------------+

Step 3: Perform calculations using SQL

Query the number of people older than 30.

SELECT COUNT(*) FROM 
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/example_table01/' 
(
  'file_format'='parquet',
  'file_pattern_blacklist'='.*_SUCCESS.*'
)
WHERE age>30;

The following result is returned:

+------------+
| _c0        |
+------------+
| 6          |
+------------+

Step 4: Export the result to OSS

You can use MaxCompute to export data to external storage, such as OSS, making it available to other computing engines. For more details, see UNLOAD.

1. Run the following command in MaxCompute to export the calculation result from Step 3 to OSS in Parquet format.

   Before you run the following code, you must create the unload_schemaless directory in the oss-mc-test OSS bucket.

   UNLOAD FROM  
   (
     SELECT COUNT(*) FROM 
     LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/example_table01/' 
     ('file_format'='parquet','file_pattern_blacklist'='.*_SUCCESS.*')
     WHERE age>30
   )
   INTO
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/unload_schemaless/'
   ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
   WITH SERDEPROPERTIES ('odps.properties.rolearn'='acs:ram::<uid>:role/AliyunODPSDefaultRole') 
   STORED AS PARQUET 
   PROPERTIES('mcfed.parquet.compression'='SNAPPY')
   ;

2. Log on to the OSS console to verify that the UNLOAD operation was successful.