0
create external table reason ( reason_id int,
  retailer_id int,
  reason_code string, 
  reason_text string,
  ordering int, 
  creation_date date,
  is_active tinyint,
  last_updated_by int,
  update_date date
  )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   "separatorChar" = "\t",
   "quoteChar"     = "'",
   "escapeChar"    = "\\"
)  
STORED AS TEXTFILE
location 's3://bucket_name/athena-workspace/athena-input/'
TBLPROPERTIES ("skip.header.line.count"="1");

Query above successfuly executes, however, there is no files in the provided location!!! Upon successful execution table is created and is empty. How is this possible?

Even if I upload file to the provided location, created table is still empty!!

Chris Williams
  • 32,215
  • 4
  • 30
  • 68
artem
  • 24
  • 2

1 Answers1

1

Athena is not a data store, it is simply a serverless tool to read data in S3 using SQL like expressions.

Amazon Athena is an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. Athena is serverless, so there is no infrastructure to manage, and you pay only for the queries that you run.

This query is creating the metadata of the table, it doesn't write to that location it reads from it.

If you put a CSV into the location and performed select * from reason it would attempt to map any CSV in the prefix of athena-workspace/athena-input/ within bucket bucket_name to your data format using the ROW FORMAT and SERDEPROPERTIES to parse the files. It would also skip the first line assuming its a header.

Chris Williams
  • 32,215
  • 4
  • 30
  • 68
  • I understand that athena doesn't store any information, it only query's it from s3. My problem is that any query in athena leads to **zero records** displayed, even though I'm sure that file in s3 has data in it. – artem Jun 10 '20 at 08:33
  • OK, your question is regarding why does Athena create table command succeed :) – Chris Williams Jun 10 '20 at 08:35
  • Sorry that is also part of it. So even if no file is stored in the provided location athena will create table anyway? – artem Jun 10 '20 at 08:36
  • Yes it will because its just a series of mappings. The first time Athena checks for data is when you perform a query against it such as `select` – Chris Williams Jun 10 '20 at 08:37
  • So ```select``` fails to parse my **.csv** file. What could be the reason to that? – artem Jun 10 '20 at 08:52
  • So check that its using a "\t" separator, quoting using "'" and escaping using "\". Also make sure the number of columns matches between table and csv. Finally make sure data types work (i.e. a field can be converted to a datetime or int) – Chris Williams Jun 10 '20 at 08:54