Search code examples
csvimportcreate-tablehiveddlqubole

Import csv file into Qubole

I am using qubole to run presto queries.

I need to upload a csv file into my query but cannot figure out how to do this.

Does anyone have any experience with this?

For more details, I am under the analyze section.

enter image description here

enter image description here

This is what I have so far based on @leftjoin's answer - 

use adhoc;
create external table adhoc.test(
  Media_Buy_Key string,
  Day string,
  DSP_Publisher string,
  Final_Media_Cost string
)
row format delimited
fields terminated by ','
lines terminated by '\n'
location 's3://bucket/folder/folder/file.csv/';

I then run the hive query and it comes up as [Empty]

This is what my s3 bucket looks like: enter image description here

Solution

  • Presto uses Hive metastore to get table information and it's data location.

    1. Upload file into some S3 location. Actually, S3 has no locations, they are emulated using filenames containing '/'. upload file using Qubole S3 interface. Say, into s3://your-bucket-name/your-location/yourfile.csv Location here is s3://your-bucket-name/your-location. If file is already in s3, you can copy it to new location using aws s3 cp command.

    2. Using Hive create table on top of your file location.

    use your_schema; create external table test( col1 string, col2 string, ... coln type ) row format delimited fields terminated by ',' lines terminated by '\n' location 's3://your-bucket-name/your-location/'; Check it works in Hive:

    select * from your_schema.test limit 10;
    1. Use Presto to query your table

    select * from your_schema.test limit 10;