MaxCompute：Tabel Eksternal JSON

Sintaksis

CREATE EXTERNAL TABLE <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS textfile
LOCATION '<oss_location>';

CREATE EXTERNAL TABLE <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
  [WITH serdeproperties (
    ['<property_name>'='<property_value>',...])
  ]
STORED AS textfile
LOCATION '<oss_location>'
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

Parameter umum

Untuk informasi lebih lanjut, lihat Parameter Sintaksis Dasar.

Parameter unik

Catatan penggunaan

Saat Anda membuat tabel eksternal JSON, jika suatu bidang berisi objek JSON bersarang (nilai bidang adalah struktur JSON), jangan mendefinisikan tipe data bidang ini sebagai STRING atau JSON. Jika tidak, sistem tidak dapat mengurai subbidang secara otomatis. Anda dapat menggunakan salah satu metode berikut:

• Definisikan bidang sebagai STRING. Saat Anda mengkueri data, Anda dapat menggunakan fungsi seperti get_json_object untuk mengekstrak konten subbidang internal sesuai kebutuhan.

• Anda dapat menggunakan tipe STRUCT untuk mendefinisikan struktur bidang. Ini memetakan subbidang objek JSON ke subkolom terpisah dalam tabel. Anda kemudian dapat langsung mengakses data internal menggunakan format field_name.subfield_name.

Untuk informasi lebih lanjut tentang operasi ini, lihat Contoh 2.

Contoh 1: Buat, tulis ke, dan kueri tabel eksternal JSON

Contoh ini menunjukkan cara membuat tabel eksternal berformat JSON menggunakan parser data open-source bawaan, menulis data ke OSS, dan mengkueri data tersebut.

1. Siapkan data.

   Masuk ke Konsol OSS dan unggah file data uji json2025.txt ke folder external-table-test/json/dt=20250521/ di dalam Bucket OSS. Untuk informasi lebih lanjut, lihat Unggah File.

2. Buat tabel eksternal berformat JSON.

   CREATE EXTERNAL TABLE mc_oss_extable_name_json
   (
     action STRING,
     time STRING
   )
   PARTITIONED BY (dt STRING)
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   STORED AS textfile
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/external-table-test/json/';

3. Temukan dan daftarkan partisi. Jika tabel eksternal OSS yang Anda buat adalah tabel partisi, Anda harus menjalankan perintah tambahan untuk mendaftarkan partisi dari direktori OSS Anda. Untuk informasi lebih lanjut, lihat Sintaksis untuk Menambahkan Data Partisi ke Tabel Eksternal OSS.

   -- Temukan dan daftarkan partisi.
   MSCK REPAIR TABLE mc_oss_extable_name_json ADD PARTITIONS;

4. Baca data dari tabel eksternal JSON.

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   Hasil berikut dikembalikan:

   +------------+------------+------------+
   | action     | time       | dt         |
   +------------+------------+------------+
   | Close      | 1469679568 | 20250526   |
   | Close      | 1469679568 | 20250526   |
   +------------+------------+------------+

5. Tulis data ke tabel eksternal JSON.

   INSERT INTO mc_oss_extable_name_json PARTITION (dt='20250526') VALUES ('test','1627273823');

6. Lihat data yang telah ditulis.

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   Hasil berikut dikembalikan:

   +------------+------------+------------+
   | action     | time       | dt         |
   +------------+------------+------------+
   | test       | 1627273823 | 20250526   |
   | Close      | 1469679568 | 20250526   |
   | Close      | 1469679568 | 20250526   |
   +------------+------------+------------+

Contoh 2: Baca nilai dari bidang berformat JSON

Siapkan Data

Buat file data JSON events.json:

{"a":{"x":1, "y":2}, "id":"123"}
{"a":{"x":3, "y":4}, "id":"345"}

Masuk ke Konsol OSS dan unggah data uji ke folder external-table-test/json-struct/ di dalam Bucket OSS. Untuk informasi lebih lanjut, lihat Unggah File.

Metode 1: Buat tabel eksternal TEXTFILE dan gunakan fungsi get_json_object untuk membaca nilai bidang

1. Buat tabel eksternal TEXTFILE yang hanya berisi satu kolom bertipe string:

   CREATE EXTERNAL TABLE extable_json_test01 (
     col STRING
   )
   ROW FORMAT DELIMITED FIELDS TERMINATED BY '\n'
   STORED AS textfile
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/external-table-test/json_struct/';  
   
   SELECT * FROM extable_json_test01;      

   Hasil berikut dikembalikan:

   +------------------------------------+
   |               col                  |
   +------------------------------------+
   | {"a": {"x": 1, "y": 2},"id":"123"} |
   | {"a": {"x": 3, "y": 4},"id":"345"} |
   +------------------------------------+

2. Anda dapat menggunakan fungsi get_json_object untuk membaca bidang a dan id:

   SELECT 
       get_json_object(col, '$.a') AS a,
       get_json_object(col, '$.id') AS id
   FROM extable_json_test01;         

   Hasil berikut dikembalikan:

   +-------------------+-----+
   |        a          | id  |
   +-------------------+-----+
   | {"x":1,"y":2}     | 123 |
   | {"x":3,"y":4}     | 345 |
   +-------------------+-----+

3. Anda dapat membaca bidang bersarang x, y, dan id:

   SELECT 
       get_json_object(get_json_object(col,'$.a'),'$.x') AS x,
       get_json_object(get_json_object(col,'$.a'),'$.y') AS y,
       get_json_object(col,'$.id') AS id
   FROM extable_json_test01;          

   Hasil berikut dikembalikan:

   +---+---+-----+
   | x | y | id  |
   +---+---+-----+
   | 1 | 2 |123  |
   | 3 | 4 |345  |
   +---+---+-----+       

Metode 2: Buat tabel eksternal JSON dan gunakan tipe STRUCT untuk mendefinisikan struktur data

1. Buat tabel eksternal berformat JSON dan gunakan tipe STRUCT untuk mendefinisikan bidang bersarang:

   CREATE EXTERNAL TABLE extable_json_test02
   (
     a STRUCT<x: BIGINT, y: BIGINT>,
     id STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   STORED AS textfile
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/external-table-test/json_struct/';          

2. Anda dapat langsung mengkueri isi tabel:

   SELECT * FROM extable_json_test02;

   Hasil berikut dikembalikan:

   +----------+-----+
   |    a     | id  |
   +----------+-----+
   | {x:1, y:2}|123 |
   | {x:3, y:4}|345 |
   +----------+-----+

3. Anda juga dapat menggunakan fungsi get_json_object dan TO_JSON untuk membaca bidang x dan y:

   SELECT 
       get_json_object(TO_JSON(a), '$.x') AS x,
       get_json_object(TO_JSON(a), '$.y') AS y,
       id
   FROM extable_json_test02;         

   Hasil berikut dikembalikan:

   +---+---+-----+
   | x | y | id  |
   +---+---+-----+
   | 1 | 2 |123  |
   | 3 | 4 |345  |
   +---+---+-----+       

Contoh 3: Tetapkan awalan, akhiran, dan ekstensi untuk file OSS keluaran

1. Tetapkan awalan kustom untuk file OSS keluaran menjadi test06_. Pernyataan DDL adalah sebagai berikut:

   CREATE EXTERNAL TABLE  <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- Tetapkan awalan kustom.
       'odps.external.data.output.prefix'='test06_') 
   ;
   
   -- Tulis data ke tabel eksternal.
   INSERT INTO  <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   Gambar berikut menunjukkan file yang dihasilkan.

   

2. Tetapkan akhiran kustom untuk file OSS keluaran menjadi _beijing. Pernyataan DDL adalah sebagai berikut:

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- Tetapkan akhiran kustom.
       'odps.external.data.output.suffix'='_beijing') 
   ;
   
   -- Tulis data ke tabel eksternal.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   Gambar berikut menunjukkan file yang dihasilkan.

   

3. Ekstensi secara otomatis dibuat untuk file OSS keluaran. Pernyataan DDL adalah sebagai berikut:

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- Secara otomatis hasilkan ekstensi.
       'odps.external.data.enable.extension'='true') 
   ;
   
   -- Tulis data ke tabel eksternal.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   Gambar di bawah ini menunjukkan file yang dihasilkan.

4. Tetapkan ekstensi kustom untuk file OSS keluaran menjadi jsonl. Pernyataan DDL adalah sebagai berikut:

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- Tetapkan ekstensi kustom.
      'odps.external.data.output.explicit.extension'='jsonl') 
   ;
   
   -- Tulis data ke tabel eksternal.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   Gambar berikut menunjukkan file yang dihasilkan.

   

5. Tetapkan awalan untuk file OSS keluaran menjadi mc_, akhirannya menjadi _beijing, dan ekstensinya menjadi jsonl. Pernyataan DDL adalah sebagai berikut:

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
       -- Tetapkan awalan kustom.
       'odps.external.data.output.prefix'='mc_', 
       -- Tetapkan akhiran kustom.
       'odps.external.data.output.suffix'='_beijing', 
       -- Tetapkan ekstensi kustom.
       'odps.external.data.output.explicit.extension'='jsonl') 
   ;  
   
   -- Tulis data ke tabel eksternal.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   Gambar berikut menunjukkan file yang dihasilkan.