Export Result Of Select Statement in Presto

A common question is “how can I run a query and export result of select statement quickly and easily in Presto?”  You are in luck, as several solutions exist – your choice will depend on your preferred tool, the output format you want, and the size of the result-set. Here are two options.

Using Presto-CLI

If you are using the Presto command line tool presto-cli (or just presto on the Mac if you used brew install presto) then use the --output-format parameter and redirect the output to a regular file. For example:

$ presto --server https://myPrestoCluster.com \
--catalog glue --schema amazon \
--execute "select product_title, count(*) as Num_Reviews from reviews where lower(product_title) like '%tacos%' group by 1 order by 2 desc limit 20" \
--output-format CSV > top20_taco_reviews.csv

$ head top20_taco_reviews.csv
"Dragons Love Tacos","832"
"Vitacost Extra Virgin Certified Organic Coconut Oil 16 Oz","281"
"Vitacost 100% Pure Peppermint Oil -- 4 fl oz","178"
"Vitacost 100% Pure Lavender Oil -- 4 fl oz","168"
"Taco Tender Holder - Plastic Red Stand - Holds 3 Tacos","106"
"Vitacost Infant Health - Baby-D's Liquid Vitamin D Drops -- 400 IU - 1 fl oz","101"
"Vitacost Butterbur Extract - Standardized -- 75 mg - 120 Capsules","84"
"Tacos, Tortas, and Tamales: Flavors from the Griddles, Pots, and Streetside Kitchens of Mexico","63"
"Vitacost Pine Bark Extract - Standardized to 95% OPC -- 100 mg - 300 Capsules","51"
"Vegan Tacos: Authentic and Inspired Recipes for Mexico's Favorite Street Food","45"

There are several formats supported by Presto-CLI, the default being quoted CSV:

--output-format <output-format>
            Output format for batch mode [ALIGNED, VERTICAL, CSV, TSV,
            CSV_HEADER, TSV_HEADER, NULL] (default: CSV)

So if you want to see column headers in your CSV format output file use --output-format CSV_HEADER

The advantage of using this approach is speed.

Using a Database Tool

If you are using a third-party SQL database tool like DbVisualizer, DBeaver or SQuirreL SQL then the UI will allow you to save the query output. For example, using DbVisualizer, run your query and click the Export button.

The advantage of this method is the huge number of output formatting options on offer. The disadvantage is it is usually slower than using Presto-CLI. 

The two options above should help you export results of a Select statement.