こんにちわ。ゆきとです。
今日はredshift-sqlを実行した時に起きたエラー文の解決策を紹介します。
Redshift sql文をpythonで実行したら...
CREATE EXTERNAL TABLE cannot run inside a transaction block
とのエラーが..
このエラーの解決策の結論を述べると、こんな感じでsql文を書けば解決します。
"""
END;
"""
以上！今日もありがとうございました！（終わらないよ）
ということで、なぜこのようになるのかを私の実例を踏まえ解説しますね。（間違ってら教えてくだしゃい）
あっと、ちなみにですが実際にSQL文を実行する時には、今回記載してる#のコメント文は消して実行してくださいね！
create_schema_ddl = """
create external schema if not exists "外部スキーマとして登録したい名前" #例 : create... exists external_shema
from data catalog
database 'AthenaなどのDataCatalogに登録されているDB名' #例 : database 'datacatalog_db'
region 'ap-northeast-1' #ここには自分のリージョンを記載してください
iam_role 'Redshiftに与えた権限'; #例 : iam_role 'arn:aws:iam:....'
"""
cursor.execute(create_schema_ddl)
create_table_ddl = """
create external table "先ほど定義した外部スキーマ".table( #例: create external ... external_shema.table(
#S3ファイル内のデータの型を事前に宣言 一列目はVARCHAR型、2列目はDOUBLE型といったように
time VARCHAR,
temperature DOUBLE PRECISION)
location '指定したいS3のフォルダパス'; #location ''s3://フォルダ名/''
"""
cursor.execute(create_table_ddl)
エラーの原因
この2つのSQL文の処理が一つのトランザクションで行われているため、sql文が正しく実行できない状態になっているのが原因でした。
つまり、DB作りながら、そのDBを参照してテーブルも作っちゃおうぜみたいな馬鹿なことをやってます（私が）
イメージしやすく例えると、作りかけの自動車に無理やり乗ろうとして「いや、まだ乗れないよ！」といわれているのと同じということです。
何をしてんだか...
これを解決するためにはそれぞれのsql文ごとにトランザクションを閉じる必要があります。
ちゃんと車を作り終えてから乗る必要があるということですね。
それらを理解した上でコードを書き直すと以下のようになります。
実際にSQL文を実行する時には、今回記載してる#のコメント文は消して実行してくださいね！
create_schema_ddl = """
create external schema if not exists "外部スキーマとして登録したい名前" #例 : create... exists external_shema
from data catalog
database 'AthenaなどのDataCatalogに登録されているDB名' #例 : database 'datacatalog_db'
region 'ap-northeast-1' #ここには自分のリージョンを記載してください
iam_role 'Redshiftに与えた権限'; #例 : iam_role 'arn:aws:iam:....'
"""
cursor.execute(create_schema_ddl)
create_table_ddl = """
END; #ここが追加部分
create external table "先ほど定義した外部スキーマ".table( #例: create external ... external_shema.table(
#S3ファイル内のデータの型を事前に宣言 一列目はVARCHAR型、2列目はDOUBLE型といったように
time VARCHAR,
temperature DOUBLE PRECISION)
location '指定したいS3のフォルダパス'; #location ''s3://フォルダ名/''
"""
cursor.execute(create_table_ddl)
create_table_ddl = """の中にEND;を入れるだけでOKです。
このEND;を入れるだけで、前回のトランザクションを終了させることができ、次回のトランザクションを開始するということができます。（実際には終わらせているだけで、次回のトランザクションを実行しているわけではないので、こんなニュアンスなんだよということを掴んでもらえると嬉しいです）
まぁ、つまり、sql文が一つの処理の中に複数個あるときは、トランザクションを意識してコードを記述しないといけない。これが今回学んだ教訓です。
それに気づけただけでも5歩前進
ということで今日の内容は以上です！
今日もブログを読んでいただき誠にありがとうございます。少しでもあなたの助けになれば幸いです。
でわでわーまた明日！