Unable To Create Table In Amazon Athena

I tried creating a table in Athena for my nested json files in s3, but receiving an error: line 1:8: no viable alternative at input 'create external' (service: amazonathena; statu

Solution 1:

It is not very clear from Athena docs wrt to restrictions on column names. However, it seems to me that your problem stems from the fact that some fields (column names) have space in them, e.g. CT Source, CT Latitude, CT Longitude and CT App Version. I managed to create table by surrounding this name with backticks.

CREATE EXTERNAL TABLE bhaskar_clevertap_2(
         eventName string,
         ts bigint,
         eventProps struct<
            ContentCategory: string,
            Previous_screen: string,
            Platform: string,
            Category: string,
            Status: string,
            `CT Source`: string,
            `CT Latitude`: int,
            `CT Longitude`: int,
            Phone: bigint,
            ADID: string,
            Email: string,
            ScreenName: string,
            DBID: bigint,
            App_version: string,
            Device_ID: string,
            `CT App Version`: string>,
         profile struct< 
             objectId: string,
             all_identities: string,
             identity: bigint,
             platform: string,
             phone: bigint,
             name: string,
             email: string,
             push_token: string>,
         deviceInfo struct<
             osVersion: int,
             sdkVersion: int,
             make: string,
             model: string,
             appVersion: string,
             browser: string,
             dpi: int>,
         dimensions struct<
             width: int,
             height: int,
             unit: string> 
) 
ROW FORMAT SERDE 
    'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
LOCATION 
    's3://clevertap-data-bhaskarapp/'