Kueri Data Parquet OSS Tanpa Skema Menggunakan MaxCompute - MaxCompute - Alibaba Cloud - MaxCompute

MaxCompute mendukung kueri tanpa skema pada tabel eksternal Parquet di OSS. Anda dapat mengekspor set data yang telah diurai ke OSS, menuliskannya ke tabel internal, atau menyematkannya sebagai subkueri dalam operasi SQL untuk pemrosesan data lake yang fleksibel.

Informasi latar belakang

Eksplorasi ad hoc terhadap data terstruktur dengan Spark tidak memerlukan model gudang data tetap. Namun, mendefinisikan skema tabel dan memetakan bidang file secara manual sebelum membaca dari atau menulis ke OSS membuat manajemen partisi menjadi rumit dan tidak fleksibel.

Dengan instruksi LOAD, MaxCompute secara otomatis mengurai file Parquet dan membaca datanya sebagai set data yang sadar skema. Anda dapat memilih kolom tertentu langsung dari set data ini untuk diproses seperti operasi tabel. Gunakan perintah UNLOAD untuk mengekspor hasil ke OSS atau perintah CREATE TABLE AS untuk mengimpornya ke tabel internal. Set data tersebut juga dapat berfungsi sebagai subkueri dalam pernyataan SQL lainnya guna mendukung operasi fleksibel pada data di data lake.

Penerapan

Kueri Tanpa Skema tidak mendukung perlakuan subdirektori dalam bucket OSS sebagai partisi.
Hanya JSONL yang didukung untuk data berformat JSON.

Sintaksis

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

Deskripsi parameter

Parameter	Wajib	Deskripsi
*	Ya	Menanyakan semua bidang dalam file.
col_name	Ya	Menanyakan bidang berdasarkan nama kolom dalam file. Tipe DECIMAL hanya mendukung `precision <=38 && scale<=18`.
table_alias.col_name	Ya	Menanyakan bidang berdasarkan nama kolom menggunakan path lengkap berupa alias tabel dan nama kolom.
table_alias	Tidak	Alias tabel kustom.
location_path	Ya	Lokasi file. Harus berupa direktori OSS dengan format `oss://oss_endpoint/bucket_name/path/`. Tingkat berikutnya di bawah path mendukung direktori partisi dalam format `partition_name=partition_value`. Kueri Tanpa Skema tidak mendukung perlakuan subdirektori di lokasi sebagai partisi, sehingga pemangkasan partisi tidak didukung.
key&value	Ya	Parameter dan nilai-nilainya untuk pernyataan kueri. Lihat tabel berikut untuk detailnya.

Tabel parameter key-value

Key	Wajib	Deskripsi	Nilai	Bawaan
file_format	Ya	Format file di lokasi. Hanya Parquet dan JSON yang didukung. Format lain akan mengembalikan error.	parquet、json	parquet
rolearn	Tidak	RoleARN yang diperlukan untuk mengakses lokasi. Login ke RAM console. Di panel navigasi kiri, pilih Identities > Roles. Di bagian Basic Information, Anda dapat memperoleh ARN. Catatan Jika Anda tidak menentukan RoleARN dalam pernyataan SQL, sistem akan menggunakan ARN dari role `AliyunODPSDefaultRole`.	`acs:ram::xxxxxx:role/aliyunodpsdefaultrole`	acs:ram::1234****:role/aliyunodpsdefaultrole
file_pattern_blacklist	Tidak	Daftar blacklist file yang akan dikecualikan. File yang namanya sesuai pola tidak akan dibaca.	`"._SUCCESS$,.\\.hive_staging.*"`	Tidak ada
file_pattern_whitelist	Tidak	Daftar whitelist file yang akan disertakan. Hanya file yang namanya sesuai pola yang akan dibaca.	`"._20250124_..parquet"`	`.*`

Contoh

Contoh 1: Membaca data OSS menggunakan parameter blacklist dan whitelist

Siapkan data.
1. Login ke OSS console.
2. Di panel navigasi kiri, klik Buckets.
3. Di halaman Buckets, klik Create Bucket.
4. Buat direktori bucket OSS object-table-test/schema/.
5. Siapkan file Parquet untuk pembacaan dan validasi whitelist. Jalankan kode Python berikut secara lokal untuk membuat file tersebut.
```
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 bucket OSS object-table-test/schema/.
  1. Login ke OSS console.
  2. Di direktori bucket object-table-test/schema/, klik Upload File.
7. Siapkan file CSV di direktori bucket OSS object-table-test/schema/ untuk memvalidasi parameter blacklist.

Baca file Parquet.

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.*'
);

Dalam kedua kasus, file sample_data dibaca. Hasil berikut dikembalikan:

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

Contoh 2: Membaca data yang ditulis oleh Spark

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

Hasilkan data Parquet menggunakan Serverless Spark. Untuk informasi lebih lanjut, lihat Buat pekerjaan SQL. Lewati langkah ini jika file Parquet yang ditulis oleh Spark sudah ada di direktori OSS.

Login ke E-MapReduce console dan pilih wilayah di pojok kiri atas.
Di panel navigasi kiri, pilih EMR Serverless > Spark.
Di halaman Spark, klik nama ruang kerja target Anda untuk membukanya. Atau, klik Create Workspace. Setelah ruang kerja baru dibuat, klik namanya untuk membukanya.

Di panel navigasi kiri, pilih Data Development, lalu buat file Spark SQL untuk menjalankan 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;

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 lebih lanjut tentang operasi Kueri Tanpa Skema, lihat Membaca data Parquet dari data lake menggunakan Kueri Tanpa Skema.

Contoh 3: Menggunakan Kueri Tanpa Skema dalam subkueri

Siapkan data.
Login ke OSS console dan unggah file uji part-00001.snappy.parquet ke direktori bucket OSS object-table-test/schema/. Untuk informasi lebih lanjut, lihat Unggah file OSS.
Login ke klien MaxCompute dan buat tabel internal untuk menyimpan data yang secara otomatis ditemukan dari OSS.
```
CREATE TABLE ow_test (
    id INT,
    name STRING,
    age INT
);
```

Gunakan Kueri Tanpa Skema untuk membaca data dari OSS.

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         |
+------------+------------+------------+

Gunakan data OSS sebagai subkueri dalam pernyataan SQL luar untuk dimasukkan ke tabel 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;

Hasil berikut 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

Siapkan data.
Login ke OSS console dan unggah file uji part-00001.snappy.parquet ke direktori bucket OSS object-table-test/schema/. Untuk informasi lebih lanjut, lihat Unggah file OSS.

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         |
+------------+------------+------------+

Gunakan pernyataan CREATE TABLE AS untuk menyalin data OSS yang secara otomatis ditemukan ke tabel internal, lalu tanyakan tabel tersebut untuk melihat hasilnya.

CREATE TABLE ow_test_2 AS 
  SELECT * FROM 
  LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/' 
  (
    'file_format'='parquet'
   );
-- Tanyakan 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 dengan UNLOAD

Siapkan data.
Login ke OSS console dan unggah file uji part-00001.snappy.parquet ke direktori bucket OSS object-table-test/schema/. Untuk informasi lebih lanjut, lihat Unggah file OSS.

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         |
+------------+------------+------------+

Gunakan perintah UNLOAD untuk mengekspor hasil yang secara otomatis ditemukan ke OSS. Untuk informasi lebih lanjut, 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: setelah perintah berhasil dijalankan, file Parquet 20250715070650775g0etr15glr2_M1_1_0_0-0_TableSink1.parquet dihasilkan di path OSS unload/ow_test_3/. Ukuran file adalah 0,449 KB dan kelas penyimpanannya adalah Standard.

Contoh 6: Membaca data berformat JSON

Hanya file JSONL yang didukung.

Unggah data uji JSONL ke OSS. sample_data.jsonl

Baca sample_data.jsonl menggunakan Kueri Tanpa Skema.

SELECT  *
FROM location 'oss://oss-<region>-internal.aliyuncs.com/<path>'
(
    'file_format'='json',
    'rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
);