Help Center> Data Warehouse Service (DWS)> 8.3.0.x> SQL on Hudi> Creating a Hudi Data Description (Foreign Table)
Updated on 2024-04-30 GMT+08:00
View PDF

Creating a Hudi Data Description (Foreign Table)

A foreign table maps data on OBS. GaussDB(DWS) accesses Hudi data on OBS through foreign tables. For details, see section CREATE FOREIGN TABLE (SQL on OBS or Hadoop).

Compared with OBS foreign tables, you do not need to specify many parameters for Hudi foreign tables. You only need to set format to hudi.

Obtaining the Definitions of Tables on MRS.

Hudi foreign tables on GaussDB(DWS) are read-only. Before creating a foreign table, you need to specify the number of fields defined in the target data and the type of each field. A Hudi foreign table supports a maximum of 5000 columns.

For example, for a Hudi table on MRS, you can use spark-sql to query the original table definitions:

1
 
SHOW create table rtd_mfdt_int_currency_t;

Compiling GaussDB(DWS) Table Definitions

Copy the definitions of all columns in the MRS table, perform proper type conversion to adapt to the GaussDB(DWS) syntax, and create an OBS foreign table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
CREATE FOREIGN TABLE rtd_mfdt_int_currency_ft(
_hoodie_commit_time text,
_hoodie_commit_seqno text,
_hoodie_record_key text,
_hoodie_partition_path text,
_hoodie_file_name text,
...
)SERVER obs_server OPTIONS (
foldername '/erpgc-obs-test-01/s000/sbi_fnd/rtd_mfdt_int_currency_t/',
format 'hudi',
encoding 'utf-8'
)distribute by roundrobin;

foldername indicates the storage path of the Hudi data on OBS, which corresponds to LOCATION in the Spark-sql table definitions of MRS. The path must end with a slash (/).

Parent topic: SQL on Hudi