SHOW EXTERNAL TABLE - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

SHOW EXTERNAL TABLE

顯示外部資料表的定義,包括資料表屬性和資料欄屬性。您可以使用 SHOW EXTERNAL TABLE 陳述式的輸出來重新建立資料表。

如需外部資料表建立的相關資訊,請參閱 CREATE EXTERNAL TABLE

語法

SHOW EXTERNAL TABLE [external_database].external_schema.table_name [ PARTITION ]

參數

external_database

相關聯外部資料庫的名稱。此為選用參數。

external_schema

相關聯外部結構描述的名稱。

table_name

要顯示的資料表名稱。

PARTITION

顯示 ALTER TABLE 敘述句,以將分割區新增至資料表定義。

範例

以下範例基於外部資料表定義如下:

CREATE EXTERNAL TABLE my_schema.alldatatypes_parquet_test_partitioned ( csmallint smallint, cint int, cbigint bigint, cfloat float4, cdouble float8, cchar char(10), cvarchar varchar(255), cdecimal_small decimal(18,9), cdecimal_big decimal(30,15), ctimestamp TIMESTAMP, cboolean boolean, cstring varchar(16383) ) PARTITIONED BY (cdate date, ctime TIMESTAMP) STORED AS PARQUET LOCATION 's3://mybucket-test-copy/alldatatypes_parquet_partitioned';

以下是 SHOW EXTERNAL TABLE 命令的範例,以及資料表 my_schema.alldatatypes_parquet_test_partitioned 的輸出。

SHOW EXTERNAL TABLE my_schema.alldatatypes_parquet_test_partitioned;
"CREATE EXTERNAL TABLE my_schema.alldatatypes_parquet_test_partitioned ( csmallint smallint, cint int, cbigint bigint, cfloat float4, cdouble float8, cchar char(10), cvarchar varchar(255), cdecimal_small decimal(18,9), cdecimal_big decimal(30,15), ctimestamp timestamp, cboolean boolean, cstring varchar(16383) ) PARTITIONED BY (cdate date, ctime timestamp) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3://mybucket-test-copy/alldatatypes_parquet_partitioned';"

以下是 SHOW 外部表命令和輸出為同一個表的一個例子,但在參數中也指定了數據庫。

SHOW EXTERNAL TABLE my_database.my_schema.alldatatypes_parquet_test_partitioned;
"CREATE EXTERNAL TABLE my_database.my_schema.alldatatypes_parquet_test_partitioned ( csmallint smallint, cint int, cbigint bigint, cfloat float4, cdouble float8, cchar char(10), cvarchar varchar(255), cdecimal_small decimal(18,9), cdecimal_big decimal(30,15), ctimestamp timestamp, cboolean boolean, cstring varchar(16383) ) PARTITIONED BY (cdate date, ctime timestamp) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3://mybucket-test-copy/alldatatypes_parquet_partitioned';"

以下是 SHOW EXTERNAL TABLE 命令的範例,以及使用 PARTITION 參數時的輸出。輸出包含 ALTER TABLE 陳述式,以將分割區新增至資料表定義。

SHOW EXTERNAL TABLE my_schema.alldatatypes_parquet_test_partitioned PARTITION;
"CREATE EXTERNAL TABLE my_schema.alldatatypes_parquet_test_partitioned ( csmallint smallint, cint int, cbigint bigint, cfloat float4, cdouble float8, cchar char(10), cvarchar varchar(255), cdecimal_small decimal(18,9), cdecimal_big decimal(30,15), ctimestamp timestamp, cboolean boolean, cstring varchar(16383) ) PARTITIONED BY (cdate date) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3://mybucket-test-copy/alldatatypes_parquet_partitioned'; ALTER TABLE my_schema.alldatatypes_parquet_test_partitioned ADD IF NOT EXISTS PARTITION (cdate='2021-01-01') LOCATION 's3://mybucket-test-copy/alldatatypes_parquet_partitioned2/cdate=2021-01-01'; ALTER TABLE my_schema.alldatatypes_parquet_test_partitioned ADD IF NOT EXISTS PARTITION (cdate='2021-01-02') LOCATION 's3://mybucket-test-copy/alldatatypes_parquet_partitioned2/cdate=2021-01-02';"