[小ネタ]Athenaのクエリ文字列制限262144 バイトはテーブル作成時に何個までカラムを指定できるか調べてみた

今回の検証で得られた参考値は、テーブルを作成時に指定できるカラム数は14348個まで、SELECT文で指定できるカラム数は27322個でした。ちなみに262144は2の18乗です。

#Amazon Athena

#AWS

西村祐二

2021.01.13

この記事は公開されてから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やテーブル名の長さなどで多少は前後するかと思いますが、おおよその数は把握できたのではないでしょうか。

誰かの参考になれば幸いです。

参考サイト

https://qiita.com/mm-Genqiita/items/e81bdcfb3b13348e146f

[小ネタ]Athenaのクエリ文字列制限262144 バイトはテーブル作成時に何個までカラムを指定できるか調べてみた

今回調べること

今回利用するテーブル作成クエリ文字列

今回利用するSQLクエリ文字列

クエリ文字列のバイト数をチェックしてみる

実際にテーブルを作成してみる

制限を回避するには

さいごに

参考サイト

関連記事

主なカテゴリ

AWS

おすすめ

プロダクト

コンテンツ

お問い合わせ

運営会社