Fitur: Kueri Tanpa Skema - MaxCompute

MaxCompute mendukung kueri tanpa skema pada data dalam tabel eksternal Parquet yang disimpan di Object Storage Service (OSS). Anda dapat mengekspor set data yang telah diurai ke OSS atau menuliskannya ke tabel internal. Selain itu, Anda juga dapat menyematkan set data tersebut sebagai subkueri dalam operasi SQL untuk mengoperasikan data di data lake dengan mudah.

Latar Belakang

Saat menggunakan Spark untuk eksplorasi ad-hoc terhadap data terstruktur, Anda tidak perlu bergantung pada model gudang data tetap. Jika harus mendefinisikan skema tabel untuk input dan output, memetakan bidang file secara manual, lalu memelihara partisi sebelum membaca atau menulis data OSS, proses tersebut menjadi rumit dan kurang fleksibel.

Saat membaca tabel eksternal Parquet, instruksi LOAD secara otomatis mengurai format file dan membaca data ke dalam set data yang memiliki skema. Hal ini memungkinkan Anda memproses data seolah-olah berada dalam tabel dengan memilih kolom tertentu. Hasilnya dapat diekspor ke OSS menggunakan perintah UNLOAD atau diimpor ke tabel internal melalui CREATE TABLE AS. Set data tersebut juga dapat digunakan sebagai subkueri dalam pernyataan SQL lainnya, sehingga memberikan cara fleksibel untuk mengoperasikan data di data lake menggunakan MaxCompute.

Catatan Penggunaan

Kueri Tanpa Skema saat ini tidak mendukung perlakuan subdirektori dalam bucket OSS sebagai partisi.

Sintaksis

SELECT *, <col_name>, <table_alias>.<col_name> 
FROM 
 LOCATION '<location_path>'  
 ('key'='value' [, 'key1'='value1', ...]) 
 [AS <table_alias>];

Parameter

Parameter	Wajib	Deskripsi
*	Ya	Kueri semua bidang dalam file Parquet.
col_name	Ya	Kueri bidang dengan nama kolom yang diketahui dalam file Parquet.
table_alias.col_name	Ya	Kueri bidang dengan nama kolom yang diketahui dalam file Parquet. Dinyatakan sebagai jalur lengkap dengan alias tabel dan nama bidang.
table_alias	Tidak	Alias tabel kustom.
location_path	Ya	Lokasi file Parquet. Harus berupa direktori OSS dengan struktur `oss://oss_endpoint/bucket_name/path/`. Tingkat di bawah path mendukung direktori partisi dalam format `partition_name=partition_value`. Kueri Tanpa Skema saat ini tidak mendukung perlakuan subdirektori dalam lokasi sebagai partisi. Oleh karena itu, pemangkasan partisi tidak didukung.
key&value	Ya	Parameter dan nilai-nilainya untuk pernyataan kueri. Untuk informasi selengkapnya, lihat tabel berikut.

Parameter key dan value

key	Wajib	Deskripsi	value	Nilai default
file_format	Ya	Menentukan format file di lokasi tersebut. Hanya Parquet yang didukung. Format lain akan menyebabkan error.	parquet	parquet
rolearn	Tidak	Menentukan RoleARN yang diperlukan untuk mengakses lokasi tersebut. Masuk ke Resource Access Management (RAM) console. Pada panel navigasi di sebelah kiri, pilih Identities > Roles. Pada bagian Basic Information, Anda dapat menemukan ARN. Catatan Jika Anda tidak menentukan RoleARN dalam pernyataan SQL, sistem akan menggunakan ARN dari role `AliyunODPSDefaultRole` secara default.	`acs:ram::xxxxxx:role/aliyunodpsdefaultrole`	acs:ram::1234****:role/aliyunodpsdefaultrole
file_pattern_blacklist	Tidak	Menentukan daftar blacklist file yang akan dibaca. Jika nama file yang dipindai cocok dengan blacklist, file tersebut tidak akan dibaca.	Ekspresi reguler. Contoh: `"._SUCCESS$,.\\.hive_staging.*"`	Tidak ada
file_pattern_whitelist	Tidak	Menentukan daftar whitelist file yang akan dibaca. File hanya akan dibaca jika namanya cocok dengan whitelist.	Ekspresi reguler. Contoh: `"._20250124_..parquet"`	`.*`

Contoh

Contoh 1: Membaca data OSS dengan mengatur parameter blacklist dan whitelist

Persiapkan data.
1. Masuk ke Object Storage Service (OSS) console.
2. Pada panel navigasi di sebelah kiri, klik Buckets.
3. Pada halaman Buckets, klik Create Bucket.
4. Buat direktori object-table-test/schema/ di bucket OSS.
5. Persiapkan file Parquet untuk dibaca dan validasi parameter whitelist. Anda dapat menjalankan kode Python berikut secara lokal untuk membuat file Parquet.
```
import pandas as pd

# Data contoh
data = [
    {'id': 3, 'name': 'Charlie', 'age': 35},
    {'id': 4, 'name': 'David', 'age': 40},
    {'id': 5, 'name': 'Eve', 'age': 28}
]

df = pd.DataFrame(data)

df['id'] = df['id'].astype('int32')
df['name'] = df['name'].astype('str')
df['age'] = df['age'].astype('int32')

output_filename = 'sample_data.parquet'
df.to_parquet(output_filename, index=False, engine='pyarrow')
```
6. Unggah file Parquet ke direktori object-table-test/schema/ di bucket OSS.
  1. Masuk ke Object Storage Service (OSS) console.
  2. Pada direktori object-table-test/schema/ di bucket, klik Upload Object.
7. Di direktori object-table-test/schema/ di bucket OSS, siapkan file CSV untuk validasi parameter blacklist.

Baca file Parquet.

Masuk ke MaxCompute client (odpscmd) dan jalankan perintah SQL berikut.

Tambahkan test_oss.csv ke blacklist dan baca file Parquet dari OSS.

SELECT id, name, age 
FROM location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/'
(
'file_format'='parquet',
'rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole',
'file_pattern_blacklist'='.*test_oss.*'
);

Tambahkan sample_data ke whitelist dan baca file Parquet dari OSS.

SELECT id, name, age 
FROM location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/'
(
'file_format'='parquet',
'rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole',
'file_pattern_whitelist'='.*sample_data.*'
);

Dengan salah satu pengaturan parameter di atas, file sample_data akan dibaca dan menghasilkan output berikut:

+------------+------------+------------+
| id         | name       | age        |
+------------+------------+------------+
| 3          | Charlie    | 35         |
| 4          | David      | 40         |
| 5          | Eve        | 28         |
+------------+------------+------------+

Contoh 2: Membaca data yang ditulis oleh Spark

Buat direktori object-table-test/spark/ di bucket OSS.

Gunakan Serverless Spark untuk menghasilkan data Parquet. Untuk informasi selengkapnya, lihat Create an SQL task. Jika file Parquet yang dihasilkan oleh Spark sudah ada di direktori OSS, Anda dapat melewati langkah ini.

Masuk ke E-MapReduce console dan pilih wilayah di pojok kiri atas.
Pada panel navigasi di sebelah kiri, pilih EMR Serverless > Spark.
Pada halaman Spark, klik nama ruang kerja target untuk membukanya. Anda juga dapat mengklik Create Workspace. Setelah ruang kerja dibuat, klik nama ruang kerja baru tersebut untuk membukanya.

Pada panel navigasi di sebelah kiri, pilih Development, buat file SparkSQL baru, lalu jalankan pernyataan SQL berikut:

CREATE TABLE example_table_parquet04 (
    id STRING,
    name STRING,
    age STRING,
    salary DOUBLE,
    is_active BOOLEAN,
    created_at TIMESTAMP,
    details STRUCT<department:STRING, position:STRING>
)
USING PARQUET;

INSERT INTO example_table_parquet04 VALUES
('1', 'Alice', '30', 5000.50, TRUE, TIMESTAMP '2024-01-01 10:00:00', STRUCT('HR', 'Manager')),
('2', 'Bob', '25', 6000.75, FALSE, TIMESTAMP '2024-02-01 11:00:00', STRUCT('Engineering', 'Developer')),
('3', 'Charlie','35', 7000.00, TRUE, TIMESTAMP '2024-03-01 12:00:00', STRUCT('Marketing', 'Analyst')),
('4', 'David', '40', 8000.25, FALSE, TIMESTAMP '2024-04-01 13:00:00', STRUCT('Sales', 'Representative')),
('5', 'Eve', '28', 5500.50, TRUE, TIMESTAMP '2024-05-01 14:00:00', STRUCT('Support', 'Technician'));

SELECT * FROM example_table_parquet04;

Masuk ke OSS console dan lihat file data yang dihasilkan di jalur tujuan.

Masuk ke client MaxCompute, tambahkan _SUCCESS ke blacklist, lalu baca file Parquet dari OSS.

SELECT  *
FROM location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/spark/example_table_parquet04/'
(
'file_format'='parquet',
'rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole',
'file_pattern_blacklist'='.*_SUCCESS.*'
);

Hasil berikut dikembalikan:

+----+---------+-----+------------+-----------+---------------------+----------------------------------------------+
| id | name    | age | salary     | is_active | created_at          | details                                      |
+----+---------+-----+------------+-----------+---------------------+----------------------------------------------+
| 1  | Alice   | 30  | 5000.5     | true      | 2024-01-01 10:00:00 | {department:HR, position:Manager}            |
| 2  | Bob     | 25  | 6000.75    | false     | 2024-02-01 11:00:00 | {department:Engineering, position:Developer} |
| 3  | Charlie | 35  | 7000.0     | true      | 2024-03-01 12:00:00 | {department:Marketing, position:Analyst}     |
| 4  | David   | 40  | 8000.25    | false     | 2024-04-01 13:00:00 | {department:Sales, position:Representative}  |
| 5  | Eve     | 28  | 5500.5     | true      | 2024-05-01 14:00:00 | {department:Support, position:Technician}    |
+----+---------+-----+------------+-----------+---------------------+----------------------------------------------+

Untuk informasi selengkapnya tentang operasi kueri tanpa skema, lihat Use a schemaless query to read Parquet data from a data lake.

Contoh 3: Menggunakan kueri tanpa skema sebagai subkueri

Persiapkan data.
Masuk ke OSS console dan unggah file data uji part-00001.snappy.parquet ke direktori bucket OSS yang ditentukan object-table-test/schema/. Untuk informasi selengkapnya, lihat Upload files to OSS.
Masuk ke client MaxCompute dan buat tabel internal untuk menyimpan data OSS yang ditemukan secara otomatis.
```
CREATE TABLE ow_test (
    id INT,
    name STRING,
    age INT
);
```

Baca data OSS menggunakan kueri tanpa skema. Perintahnya sebagai berikut:

SELECT * FROM 
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/' 
(
  'file_format'='parquet'
);

Hasil berikut dikembalikan:

+------------+------------+------------+
| id         | name       | age        |
+------------+------------+------------+
| 3          | Charlie    | 35         |
| 4          | David      | 40         |
| 5          | Eve        | 28         |
+------------+------------+------------+

Anda dapat meneruskan data yang dibaca dari OSS sebagai subkueri ke pernyataan SQL luar dan mengkueri tabel hasil ow_test.

INSERT OVERWRITE TABLE ow_test
SELECT id,name,age FROM 
(
    SELECT * FROM 
    LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/' 
    (
      'file_format'='parquet'
    )
);

SELECT * FROM ow_test;

Berikut adalah hasil yang dikembalikan:

+------------+------------+------------+
| id         | name       | age        |
+------------+------------+------------+
| 3          | Charlie    | 35         |
| 4          | David      | 40         |
| 5          | Eve        | 28         |
+------------+------------+------------+

Contoh 4: Menyimpan hasil kueri tanpa skema ke tabel gudang data internal

Persiapkan data.
Masuk ke OSS console dan unggah file data uji part-00001.snappy.parquet ke direktori bucket OSS yang ditentukan object-table-test/schema/. Untuk informasi selengkapnya, lihat Upload files to OSS.

Masuk ke client MaxCompute dan baca data OSS menggunakan kueri tanpa skema.

SELECT * FROM 
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/' 
(
  'file_format'='parquet'
);

Hasil berikut dikembalikan:

+------------+------------+------------+
| id         | name       | age        |
+------------+------------+------------+
| 3          | Charlie    | 35         |
| 4          | David      | 40         |
| 5          | Eve        | 28         |
+------------+------------+------------+

Salin data OSS yang ditemukan secara otomatis ke tabel internal menggunakan pernyataan CREATE TABLE AS dan kueri hasilnya.

CREATE TABLE ow_test_2 AS 
  SELECT * FROM 
  LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/' 
  (
    'file_format'='parquet'
   );

-- Kueri tabel hasil ow_test_2
SELECT * FROM ow_test_2;

Hasil berikut dikembalikan:

+------------+------------+------------+
| id         | name       | age        |
+------------+------------+------------+
| 3          | Charlie    | 35         |
| 4          | David      | 40         |
| 5          | Eve        | 28         |
+------------+------------+------------+

Contoh 5: Mengekspor hasil kueri tanpa skema kembali ke data lake

Persiapkan data.
Masuk ke OSS console dan unggah file data uji part-00001.snappy.parquet ke direktori bucket OSS yang ditentukan object-table-test/schema/. Untuk informasi selengkapnya, lihat Upload files to OSS.

Masuk ke client MaxCompute dan baca data OSS menggunakan kueri tanpa skema.

SELECT * FROM 
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/' 
(
  'file_format'='parquet'
);

Hasil berikut dikembalikan:

+------------+------------+------------+
| id         | name       | age        |
+------------+------------+------------+
| 3          | Charlie    | 35         |
| 4          | David      | 40         |
| 5          | Eve        | 28         |
+------------+------------+------------+

Ekspor hasil yang ditemukan secara otomatis ke OSS. Untuk informasi selengkapnya tentang operasi UNLOAD, lihat UNLOAD.

UNLOAD FROM (
  SELECT * FROM 
  LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/' 
  ('file_format'='parquet')
) 
INTO 
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/unload/ow_test_3/'
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
WITH SERDEPROPERTIES ('odps.external.data.enable.extension'='true')
STORED AS PARQUET;

Lihat file yang dihasilkan di direktori OSS: