Issue with account mapping queries.

Question

Hi,

I'have deployed dashboards and trying to mapping account id with names. I'm following this [link](https://wellarchitectedlabs.com/cost/200_labs/200_cloud_intelligence/cost-usage-report-dashboards/dashboards/code/0_view0/) 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.

Accepted Answer

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](/media/postImages/original/IMvPr2Eg4OSRWHwLf1WzKjpA)

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

Issue with account mapping queries.

Relevant content