Issue with account mapping queries.

0

Hi,

I'have deployed dashboards and trying to mapping account id with names. I'm following this link of Option 2 by using s3 file.

When i try to run below query it gives me error

Cannot create table on file, please specify a directory This query ran against the "cid_cur" database, unless qualified by the query

After adding db with table like 'cid_cur'.'account_mapping' it gives error.

line 1:8: mismatched input 'EXTERNAL'. Expecting: 'MATERIALIZED', 'OR', 'ROLE', 'SCHEMA', 'TABLE', 'VIEW'

 CREATE EXTERNAL TABLE IF NOT EXISTS account_mapping(
   account_id string, account_name string, environment string, business_unit string, team 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://account_map.csv'
 TBLPROPERTIES (
   'has_encrypted_data'='false',
   'skip.header.line.count'='1')

I got stuck and tried many ways to fix it. Please help if I'm missing anything.

1回答
4
承認された回答

Assuming, you are doing it using option-2, can you redo the steps and this time specify a directory for account_map.csv file. If you have already followed the directory structure as outlined in Well Architected Lab, then you just need to modify your query where in Location, you need not to give account_map.csv file uri but the directory where this file exists.

I see in your query, you have mentioned file location, you need to provide the directory name instead where this file exists.

I'm highlighting that in following snapshot:

Enter image description here

Also, double check the columns, as I see the difference in your query and the one which is there in Well Architected Lab/Snapshot above.

Hope this helps.

Comment here if you have additional questions, happy to help.

Abhishek

profile pictureAWS
エキスパート
回答済み 1年前
profile pictureAWS
エキスパート
レビュー済み 1年前

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ