この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
どうも!大阪オフィスの西村祐二です。
最近、Amazon Athenaを触る機会がありました。
自動でテーブルを作成したり、自動でクエリ実行したりする仕組みを作ったりしています。
そこで、気になるところとしてはサービス制限です。
ドキュメントを確認すると、テーブルを作成したり、SQLクエリ実行したりするときのクエリの制限として下記の制限があるようです
許容される最大クエリ文字列の長さは、262144 バイトです。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/service-limits.html
この制限ですが、実際にどれぐらいの数まで実行できるのかイメージできなかったため少し調べてみました。
今回調べること
- テーブル作成時に何個までカラムを指定できるか
-
SELECT文に何個までカラムを指定できるか
今回利用するテーブル作成クエリ文字列
今回は下記のようなサンプルを用意しました。
このサンプルをもとに、どれだけカラム数を増やしていけるかを調べていきます。
CREATE EXTERNAL TABLE IF NOT EXISTS testdb.test_table(`test0` double,`test1` double.......)
PARTITIONED BY(
`year` string,
`month` string,
`day` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://test_bucket/'
TBLPROPERTIES(
'classification'='csv',
'columnsOrdered'='true',
'compressionType'='gzip',
'delimiter'=',',
'skip.header.line.count'='1'
);
今回利用するSQLクエリ文字列
今回は下記のようなサンプルを用意しました。
このサンプルをもとに、どれだけカラム数を増やしていけるかを調べていきます。
select test0, test1 ..... from testdb.test_table
クエリ文字列のバイト数をチェックしてみる
pythonでクエリ文字列のバイト数を確認して何個まで作成できるか数えるスクリプトを作りました。
DB名,テーブル名、バケット名などは適宜変更してください。
ATHENA_DB = 'testdb'
ATHENA_TABLE = 'test_table'
RETRY_COUNT = 10
BUCKET = '<your bucket name>'
columns = []
query_string_byte = 0
limit_byte_size = 262144
counter = 0
def add_sql_column():
columns.append(f'test{len(columns)}')
def add_table_column():
columns.append(f'`test{len(columns)}` double')
def sql_query(columns):
return f'select {columns} from {ATHENA_DB}.{ATHENA_TABLE}'
def create_table(columns):
athena_ddl = f'''CREATE EXTERNAL TABLE IF NOT EXISTS {ATHENA_DB}.{ATHENA_TABLE}({columns})
PARTITIONED BY(
`year` string,
`month` string,
`day` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://{BUCKET}/'
TBLPROPERTIES(
'classification'='csv',
'columnsOrdered'='true',
'compressionType'='gzip',
'delimiter'=',',
'skip.header.line.count'='1'
);
'''
return athena_ddl
def get_byte_size(sql):
return len(sql)
if __name__ == "__main__":
# 何個のカラム指定できるか計測
while query_string_byte < limit_byte_size:
# add_sql_column()
add_table_column()
columns_list = f'{",".join(columns)}'
# query = sql_query(columns_list)
query = create_table(columns_list)
query_string_byte = get_byte_size(query)
counter += 1
print(f'counter:{counter}')
それぞれ実行した結果から、今回のサンプルでは下記のようになりました。
- テーブル作成時に指定できるカラム数は
14348個
まで
- SELECT文で指定できるカラム数は
27322個
まで
実際にテーブルを作成してみる
実際に、テーブル作成時にカラムを14348個指定して作成できるかどうか確かめてみます。
pythonでテーブルを作成するスクリプトを作りました。
import time
import boto3
ATHENA_DB = 'testdb'
ATHENA_TABLE = 'test_table'
RETRY_COUNT = 10
BUCKET = '<your bucket name>'
client = boto3.client('athena', region_name='ap-northeast-1')
columns = []
query_string_byte = 0
counter = 0
def add_sql_column():
columns.append(f'test{len(columns)}')
def add_table_column():
columns.append(f'`test{len(columns)}` double')
def sql_query(columns):
return f'select {columns} from {ATHENA_DB}.{ATHENA_TABLE}'
def create_table(columns):
athena_ddl = f'''CREATE EXTERNAL TABLE IF NOT EXISTS {ATHENA_DB}.{ATHENA_TABLE}({columns})
PARTITIONED BY(
`year` string,
`month` string,
`day` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://{BUCKET}/'
TBLPROPERTIES(
'classification'='csv',
'columnsOrdered'='true',
'compressionType'='gzip',
'delimiter'=',',
'skip.header.line.count'='1'
);
'''
return athena_ddl
def get_byte_size(sql):
return len(sql)
def query_execute(sql):
"""クエリー実行."""
qid = __execute_athena(sql)
print(qid)
__check_result(qid)
return qid
def __execute_athena(athena_ddl):
"""athenaへクエリを実行する."""
qid = client.start_query_execution(
QueryString=athena_ddl,
QueryExecutionContext={
'Database': ATHENA_DB
},
ResultConfiguration={
'OutputLocation': f's3://{BUCKET}'
}
)
return qid['QueryExecutionId']
def __check_result(qid):
"""クエリーが成功したかステータス確認."""
for i in range(1, 1 + RETRY_COUNT):
# get query execution
query_status = client.get_query_execution(
QueryExecutionId=qid)
# print(query_status)
query_execution_status = query_status['QueryExecution']['Status']['State']
if query_execution_status == 'SUCCEEDED':
print("STATUS:" + query_execution_status)
break
if query_execution_status == 'FAILED':
raise Exception("STATUS:" + query_execution_status)
else:
print("STATUS:" + query_execution_status)
time.sleep(i)
else:
client.stop_query_execution(QueryExecutionId=qid)
raise Exception('TIME OVER')
if __name__ == "__main__":
for i in range(14348):
add_table_column()
columns_list = f'{",".join(columns)}'
query = create_table(columns_list)
query_execute(query)
実行すると下記ログが出力されて問題なく作成できました
xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx
STATUS:RUNNING
STATUS:RUNNING
STATUS:RUNNING
STATUS:SUCCEEDED
マネージメントコンソール上から確認してもきちんとカラムが追加された状態でテーブルが作成されていました。
ちなみに14349にすると下記エラーが出力されました。
' at 'queryString' failed to satisfy constraint: Member must have length less than or equal to 262144
制限を回避するには
制限の262144バイト自体を大きくすることはできないようです。
回避するには、いくつかにクエリを分割して実行するなど対処が必要になってきます。
https://aws.amazon.com/jp/premiumsupport/knowledge-center/athena-query-string-length/
さいごに
Athenaの制限としてクエリ文字列の長さは、262144 バイトまでという制限があります。
テーブルを作成したり、SQLクエリ実行したりするとき、実際にどれぐらいの数まで実行できるのか調べてみました。
今回のサンプルでは下記のような結果になりました。
- テーブル作成時に指定できるカラム数は
14348個
まで
- SELECT文で指定できるカラム数は
27322個
まで
カラム名の長さ、DBやテーブル名の長さなどで多少は前後するかと思いますが、おおよその数は把握できたのではないでしょうか。
誰かの参考になれば幸いです。