Treasure Data - Support Engineering Team blog

トレジャーデータのサポートエンジニアリングチームのブログです。

Columnを削除したい

こんにちは、Treasure Data(以後TD)サポートの伊藤です。

TDのテーブルからColumn(列)を削除したいというリクエストを度々頂戴します。本記事では関連したナレッジなどを整理しつつ、どのように対応すれば良いのか紹介していきます。

結論

TDではColumnを物理的に削除する機能はありません。 その理由や代替策などについて説明していきます。

Columnを削除してみる(できたように見えるだけ)

まず、実際にColumn削除を試してみましょう。 ここでは3つ方法を紹介します。

TDコンソールのUI操作でColumn削除

TDコンソール(Web UI)では、下記手順でColumn削除することができます。

  1. Data Workbench(歯車アイコンをクリック)
  2. Databases をクリック
  3. Columnを削除したい対象テーブルが属するデータベースをクリック
  4. Tablesタブにて対象テーブルをクリック
  5. Schemaタブをクリック
  6. Edit Schema をクリック
  7. 対象Columnの右端にある × をクリック
  8. Save をクリック

f:id:td-support:20210716184358p:plain
TDコンソールでColumnを削除する

PrestoでColumn削除

Prestoでは ALTER TABLE ... DROP COLUMN 文を実行すると対象Columnを削除することができます。

ALTER TABLE <target_table_name> DROP COLUMN <target_column_name>;

f:id:td-support:20210716185012p:plain
PrestoクエリでColumnを削除する

3 CLI(Toolbelt)でColumn削除

CLI(Toolbelt)では td schema:remove コマンドで対象Columnを削除することができます。

$ td schema:remove <target_database> <target_table> <target_column>

Columnを物理削除できていない

これらの方法では一見Column削除できているように見えます。 テーブルのColumn定義を確認すると、削除した col3 は存在しません。

f:id:td-support:20210716185505p:plain
col3がColumn定義から消えている

また、SELECT * にて対象テーブルを参照しても col3 は結果として返されません。

f:id:td-support:20210716185530p:plain
SELECT * で col3 が結果として返されない

ですが、残念ながら対象Columnを実際には削除できていないのです。どういうことか、見ていきましょう。

削除したColumnを復旧する

TDコンソールを利用して削除したColumnを復旧することができます。 手順としては下記になります。

  1. Data Workbench(歯車アイコンをクリック)
  2. Databases をクリック
  3. Columnを削除したい対象テーブルが属するデータベースをクリック
  4. Tablesタブにて対象テーブルをクリック
  5. Schemaタブをクリック
  6. Edit Schema をクリック
  7. Add Column をクリック
  8. COLUMN、QUERY AS、TYPEを入力(削除時のものを入力)
  9. Save をクリック

f:id:td-support:20210716185908p:plain
col3を追加してみる

作業完了後に SELECT 文を実行してみましょう。すると、削除したはずのColumn自体も、格納していたデータも参照できることがわかります。

f:id:td-support:20210716190230p:plain
col3を再度参照できるようになっている

なぜ削除されないのか(復旧できてしまうのか)

Column定義(Column名やそのデータ型)のことをTDではSchema(スキーマ)と呼びます。

Schemaは格納した実データとは別に保持されていて独立しています。

そしてTDではSchema on Read(スキーマオンリード)というフレームワークが採用されています。その名の通りデータを読む(Read)する際にSchemaを参照し、合致しないデータはNULLとするという仕組みです。

このフレームワークは、導入初期にSchema(Column定義)を確定させる必要がない、Schema変更時にロックを取得しないので柔軟に変更できる、実データを変更しないため変更作業が短時間で完了するなどのメリットが挙げられます。 一方、この仕組みに起因して、Columnを削除できたように見えて実はできていないという事象が発生します。

具体的な例を使って見ていきましょう。 例えば、下記のようにデータが格納されているとします。

{"col1":1,"col2":"test1","time":1625562000}
{"col1":2,"col2":"test2","time":1625562000}

Columnとして下記3つを想定して格納されているのがわかるかと思います。

  • col1
  • col2
  • time

f:id:td-support:20210716191222p:plain
格納されているデータ

ケース1 格納データとSchemaが合致

この状況でSchemaで下記が設定されていると、SELECT * FROM target_table を実行すると3つのColumnを取得できます。

  • col1: long
  • col2: string
  • time: long

これは、格納されているデータとSchemaが合致しているからになります。

f:id:td-support:20210716193211p:plain
格納データとSchemaが一致

ケース2 格納データよりColumn数が多い

Schema設定でColumnを増やしてみます。

  • col1: long
  • col2: string
  • col3: double
  • time: long

SELECT * FROM target_table; を実行すると4つのColumnを取得できます。 これは、格納されているデータをSchemaを突き合わせて、col3はデータとしては存在しないのでNULLを返すという挙動になっています。

f:id:td-support:20210716193627p:plain
データが格納されていないColumnを定義

ケース3 格納データよりColumn数が少ない

最後に下記の様にShcema設定でColumnを減らしてみます。

  • col1: long
  • time: long

SELECT * FROM target_table; を実行すると2つのColumnのみ取得でき、Schema定義に存在しないcol2は結果として取得できません。 これはSchema定義を通して取得するColumnを決めているので、Schema定義上存在しないColumnは参照できないからです。

f:id:td-support:20210716194051p:plain
データが格納されているcol2をSchemaから削除

SELECT col2 FROM target_table; を実行すると、col2Columnはデータとしては格納されているのですが Schema上存在しないため、 Column 'col2' cannot be resolved というエラーで失敗します。

Column削除の代替策

SELECT * で抽出されなければ良い、SELECT <column_name> で参照できなければ良いのであれば、先述した方法をお使いください。

一方で、特定のColumnを完全に削除したいというケースがあるかと思います。その場合はどうすれば良いのでしょうか?

テーブルを作り変える

Columnを削除ということは実現できないため、代替策を紹介します。 具体的には、テーブルを作成しなおすことで代替できます。

現行テーブルが下記だとします。col3を削除することを目指します。

col1 col2 col3 time
1 test1 1.1 1625562000
2 test2 2.2 1625562000
3 test3 3.3 1625562000

まず、col3を削除したデータを別テーブルにコピーします。Prestoならば下記のようなクエリ文で良いでしょう。

CREATE TABLE target_db.deleted_table AS
SELECT col1,
       col2,
       time
  FROM target_db.current_table --現行テーブル
;

次に、現行テーブルと上記で作成したテーブルを入れ替えます。REST API/v3/table/swap/:database/:table1/:table2 を利用すると良いでしょう。

api.treasuredata.com はTDのリージョンがUSの場合のエンドポイントです

$ curl -H "AUTHORIZATION: TD1 <api_key>" "https://api.treasuredata.com/v3/table/swap/target_db/current_table/deleted_table"

もしくは TD Toolbelt(CLI)の td table:swap コマンドをお使いください。

$ td table:swap target_db current_table deleted_table

この手順を踏むことで col3 を擬似的に削除することができます。

最後に

いかがでしたでしょうか?

Column削除する目的によって下記使い分けていただければと思います。

  • クエリで参照できなくなれば十分な場合: TDコンソールやクエリで削除
  • 物理的に削除する必要がある場合: テーブル再作成