无法在Amazon Athena中创建表 [英] Unable to Create Table in Amazon Athena

问题描述

我尝试在Athena中为s3中的嵌套json文件创建一个表，但收到错误:

I tried creating a table in Athena for my nested json files in s3, but receiving an error:

第1行:8:输入创建外部"时没有可行的替代方法(服务:amazonathena；状态代码:400；错误代码:invalidrequestexception；请求ID:dcea57c7-b801-41a4-8edb-e42eb1608fea)

line 1:8: no viable alternative at input 'create external' (service: amazonathena; status code: 400; error code: invalidrequestexception; request id: dcea57c7-b801-41a4-8edb-e42eb1608fea)

表名和字段似乎还可以，并且还附有我s3源中的示例json.参考了雅典娜的官方文件.另外，请让我知道是否以及如何从Athena的创建表"向导中添加嵌套字段.

Table name and fields seem okay, also have attached sample json from my s3 source. Have referred the official documentation of Athena. Also, let me know if and how can I add nested fields from Create Table wizard in Athena.

我的查询是:

    CREATE EXTERNAL TABLE bhaskar_clevertap(
         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/' 

示例json对象看起来像:

A sample json object would loook like:

{
"eventName": "ScreenView",
"ts": 20200106073433,
"eventProps": {
    "ContentCategory": "ePaper",
    "Previous_screen": "",
    "Platform": "Android",
    "Category": "ePaper",
    "Status": "LoggedIn",
    "CT Source": "Mobile",
    "CT Latitude": 25.613659,
    "CT Longitude": 85.101135,
    "Phone": "[phone]",
    "ADID": "fc2adc9b-6e31-459c-9bee-d6039a263e5c",
    "Email": "[e-mail]",
    "ScreenName": "ePaper_BIHAR_पटना_पटना_Page3",
    "DBID": "1008894402",
    "App_version": "6.1.0",
    "Device_ID": "3fccfa7749a8fba2",
    "CT App Version": "6.1.0"
},
"profile": {
    "objectId": "__gfc2adc9b6e31459c9beed6039a263e5c",
    "all_identities": ["7678284***", "1008894402@****"],
    "identity": "7678284***",
    "platform": "Android",
    "phone": 917678284***,
    "name": "NA",
    "email": "1008894402@****",
    "push_token": "fcm:dAI-w-gLwyk:APA91bE5E3pz63NMnV9rxqMwYTsOtQnmwjI8ZdElIX98fo0qiCPVnvekDwMMt27tAuI1F5PZmufw1OLpC_6V_hE_n_9eUVn8tF7EqA9MUu80wionD2FYtj*****FrSQDnDJoTFVZFmqg"
},
"deviceInfo": {
    "osVersion": "7.0",
    "sdkVersion": "30501",
    "make": "Xiaomi",
    "model": "Redmi Note 4",
    "appVersion": "6.1.0",
    "browser": "MobileApp",
    "dpi": 480,
    "dimensions": {
        "width": 69,
        "height": 121,
        "unit": "mm"
    }
}

}

请帮助我查找查询问题.

Kindly help me finding issues with my query.

推荐答案

从

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/' 

这篇关于无法在Amazon Athena中创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！