CODEWITHSUNDEEP
amazon-s3hdfsamazon-emrimpala
Kyle Gobel
Kyle Gobel

Reputation: 5750

How to save the results of an impala query

I've loaded a large set of data from S3 into hdfs, and then inserted the data to a table in impala.

I then ran a query against this data, and I'm looking to get these results back into S3.

I'm using Amazon EMR, with impala 1.2.4. If it's not possible to get the results of the query back to S3 directly, are there options to get the data back to hdfs and then some how send it back to S3 from there?

I have messed around with the impala-shell -o filename options, but that appears to only work on the local linux file system.

I thought this would have been a common scenario, but having trouble finding any information about saving the results of a query anywhere.

Any pointers appreciated.

Upvotes: 3

Views: 5461

Answers (3)

Vijay Krishna
Vijay Krishna

Reputation: 1067

To add to the knowledge above I am including the command that writes the query results to a file with a delimeter as we declared using the option --output_delimeter and also by using the option --delimeted which actually switches off the default tab delimeter option. 

  impala-shell -q "query " --delimited   --output_delimiter='\001'  --print_header -o 'filename'

Upvotes: 2

Kali
Kali

Reputation: 1

If you have aws cli installed you can use standard out of Impala shell , impala shell query | aws cli cp - s3folder/outputfilename You can use unix pipe and stream (-)

Upvotes: 0

maxymoo
maxymoo

Reputation: 36545

What I usually do if it's a smallish result set is run the script from the command line then upload to s3 using the AWS command line tool:

impala-shell -e "select ble from bla" -o filename
aws s3 cp filename s3://mybucket/filename

An alternative is use Hive as the last step in your data pipeline after you've run your query in Impala:

1. Impala step:

create table processed_data
as
select blah
--do whatever else you need to do in here
from raw_data1
join raw_data2 on a=b

2. Hive step:

create external table export
like processed_data
location 's3://mybucket/export/';

insert into table export
select * from processed_data;

Upvotes: 1

Related Questions