Data Lake Formation:Akses DLF dari Hologres

Buat skema eksternal di katalog

Saat Anda membuat skema eksternal, sebuah database dibuat di Katalog DLF. Contoh kode berikut:

-- Create an external schema.
CREATE EXTERNAL SCHEMA [IF NOT EXISTS] <ext_database_name>.<ext_schema_name>;

Segarkan metadata skema eksternal

REFRESH CACHE FOR EXTERNAL SCHEMA <ext_database_name>.<ext_schema_name> WITH( cache_level = 'metadata'); 

Hapus skema eksternal

Saat Anda menghapus skema eksternal, database yang sesuai dan semua tabel di dalamnya dihapus dari katalog. Lakukan operasi ini dengan hati-hati.

-- Delete an external schema.
DROP EXTERNAL SCHEMA [IF NOT EXISTS] <ext_database_name>.<ext_schema_name>;

Lihat tabel di skema tertentu

SELECT * FROM hologres.hg_external_tables ('<ext_database_name>', '<ext_schema_name>');

Buat tabel eksternal di katalog

-- Create a non-partitioned table.

CREATE EXTERNAL TABLE <ext_database_name>.<ext_schema_name>.<ext_table_name>(
    id TEXT,                                        
    created_at BIGINT,                             
    type TEXT,                                      
    actor_id TEXT,                                  
    actor_login TEXT,                               
    repo_id TEXT,                                   
    repo_name TEXT,                                 
    org TEXT,                                       
    org_login TEXT,                                 
    PRIMARY KEY(id)
) WITH (
  "changelog-producer"='input',
  "bucket"=6,
  "bucket-key"='id'
);

-- Create a partitioned table.

CREATE EXTERNAL TABLE <ext_database_name>.<ext_schema_name>.<ext_table_name>(
    id TEXT,                                        
    created_at BIGINT,                              
    type TEXT,                                      
    actor_id TEXT,                                  
    actor_login TEXT,                               
    repo_id TEXT,                                  
    repo_name TEXT,                                 
    org TEXT,                                       
    org_login TEXT                                
)
LOGICAL PARTITION BY LIST(created_at)
 WITH (
  "file_format" = 'orc',
  "bucket"=6,
  "bucket-key"='id'
);

Segarkan metadata tabel eksternal

REFRESH CACHE FOR EXTERNAL TABLE <ext_db_name>.<ext_schema_name>.<ext_table_name> WITH( cache_level = 'metadata');

Hapus tabel eksternal

Saat Anda menghapus tabel eksternal, tabel tersebut dihapus dari Katalog DLF. Lakukan operasi ini dengan hati-hati.

-- Delete an external table.
DROP EXTERNAL TABLE IF EXISTS <ext_database_name>.<ext_schema_name>.<ext_table_name>;

Kumpulkan informasi statistik tabel eksternal

Perintah ANALYZE dan AUTO ANALYZE mengumpulkan informasi statistik tentang konten tabel di database. Pengoptimal menggunakan informasi ini untuk menghasilkan rencana kueri yang optimal dan meningkatkan efisiensi kueri. Untuk informasi selengkapnya, lihat ANALYZE and AUTO ANALYZE.

-- Update the statistics information for a table. By default, statistics information is collected for all columns in the table.
analyze <ext_database_name>.<ext_schema_name>.<ext_table_name>;

-- Update the statistics information for a specific column. More data is sampled than when updating the entire table, which results in more accurate statistics. This is mainly used to update columns with conditional filters.
analyze <ext_database_name>.<ext_schema_name>.<ext_table_name>(<colname>, <colname>);

-- Enable auto-analyze.
ALTER EXTERNAL DATABASE <ext_database_name> WITH enable_auto_analyze 'true';

Kueri tabel sistem Paimon Snapshots

SELECT * FROM hologres.hg_list_snapshots('<ext_database_name>.<ext_schema_name>.<ext_table_name>;');

Tabel berikut menjelaskan pemetaan antara bidang di tabel sistem Snapshots dan bidang di tabel sistem Paimon.

Anda juga dapat menggunakan fungsi hologres.hg_list_versions untuk mengkueri tabel sistem Paimon Snapshots. Untuk kueri tabel Paimon, versi bersesuaian dengan ID snapshot.

SELECT * FROM hologres.hg_list_versions('<ext_database_name>.<ext_schema_name>.<ext_table_name>');

Kueri tabel sistem Paimon Tag

SELECT * FROM hologres.hg_list_tags('<ext_database_name>.<ext_schema_name>.<ext_table_name>');

Tabel berikut menjelaskan pemetaan antara bidang di tabel sistem Tag dan bidang di tabel sistem Paimon.

Kueri snapshot historis tabel Paimon berdasarkan stempel waktu

SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR TIMESTAMP AS OF '<timestamp>';

Kueri snapshot historis tabel Paimon berdasarkan versi

Untuk tabel Paimon, versi bersesuaian dengan ID snapshot.

SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR VERSION AS OF '<version>';

Kueri snapshot historis tabel Paimon berdasarkan tag

SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR TAG AS OF '<tag>';

Kueri tabel sistem Branch

SELECT * FROM hologres.hg_list_branches('<ext_database_name>.<ext_schema_name>.<ext_table_name>');

Tabel berikut menjelaskan pemetaan antara bidang di tabel sistem Branch dan bidang di tabel sistem Paimon.

Kueri data cabang tertentu

SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR branch AS OF '<tag>';

Kueri data snapshot historis cabang tertentu

-- Query based on a timestamp.
SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR branch AS OF '<branch_name>' TIMESTAMP AS OF '<timestamp>';

-- Query based on a tag.
SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR branch AS OF '<branch_name>' TAG AS OF '<tag_name>';

Penyegaran penuh

CREATE DYNAMIC TABLE paimon_dt_table
WITH (
      auto_refresh_mode='full',
      freshness='3 minutes'
)AS SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;

Penyegaran inkremental

CREATE DYNAMIC TABLE paimon_dt_table_increamental
WITH (
      auto_refresh_mode='incremental',
      freshness='3 minutes'
)AS SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;

Penyegaran otomatis

CREATE DYNAMIC TABLE paimon_dt_table_auto
WITH (
      auto_refresh_mode='auto',
      freshness='3 minutes'
)AS SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;