Glue カタログでテーブルのカラムを追加・削除すると、Redshift Spectrum でも追加・削除されるという当たり前のことを検証してみたメモ。
カラム追加・削除
- Redshift から Glue カタログを参照するため external schema を作成する。
create external schema spectrum_schema from data catalog database 'tpc-h_10gb_parquet' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' region 'us-east-1';
- Glue カタログのテーブル定義。col1 が存在する状態。
- Redshift Spectrum からクエリすると col1 は表示される。
dev awsuser 20230602_18:39:37 =# select * from spectrum_schema.nation_tbl; col0 | col1 | col2 | col3 --------+----------------+------+-------------------------------------------------------------------------------------------------------------------- 0.000 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai 1.000 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 2.000 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special 3.000 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold
- Glue カタログのテーブル定義で col1 を削除する。
- Redshift Spectrum からクエリすると col1 は表示されない。
dev awsuser 20230602_18:39:52 =# select * from spectrum_schema.nation_tbl; col0 | col2 | col3 --------+------+-------------------------------------------------------------------------------------------------------------------- 0.000 | 0 | haggle. carefully final deposits detect slyly agai 1.000 | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 2.000 | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special 3.000 | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold
桁数変更
- 元は col1 は varchar(20) のカラム
- Redshift Spectrum で参照すると 20 桁で表示される
dev awsuser 20230606_06:20:39 =# select * from spectrum_schema.nation_tbl; col0 | col1 | col2 | col3 --------+----------------+------+-------------------------------------------------------------------------------------------------------------------- 0.000 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai 1.000 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 2.000 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special 3.000 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold 4.000 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d ...
- col1 を varhcar(20) から varchar(2) に桁数を変更する
- col1 は 2桁しか表示されなくなる（Spark でアクセスすると桁が切り詰められずに表示される）
dev awsuser 20230606_06:24:08 =# select * from spectrum_schema.nation_tbl; col0 | col1 | col2 | col3 --------+------+------+-------------------------------------------------------------------------------------------------------------------- 0.000 | AL | 0 | haggle. carefully final deposits detect slyly agai 1.000 | AR | 1 | al foxes promise slyly according to the regular accounts. bold requests alon 2.000 | BR | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special 3.000 | CA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold 4.000 | EG | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d ...
