Data Tanks Using Presto

Use Presto, via TD Console, to query data stored in your Treasure Data Data Tank.

This optional feature enables users of Data Tanks to run queries against their Data Tank instance directly from the Presto query engine.

What is Data Tank?

Data Tanks provide easy access to your aggregated metrics through convenient, fully hosted data marts on Treasure Data’s core platform. They can be used to drive a variety of external business intelligence and visualization applications without having to host and maintain your own PostgreSQL instances.

Data Tanks are hosted PostgreSQL databases that serve as data marts. They are completely managed by Treasure Data including creation, setup, monitoring, management and troubleshooting so you can just get your job done.

Benefits

Use of Data Tanks provides the following key benefits:

• Enables easier access to Data Tank data via querying from TD Console, via Presto. Including the creation and deletion of tables.
• More flexible processing flows by enabling data to be joined across Data Tank and core TD tables.

Learn More

• Limitations on Data Tank Queries from Presto
• Data Tank User Permissions for Presto Queries
• Granting Access for Data Tank Queries from Presto
• Data Tank Query Statement Reference for Presto
• Data Tank Presto FAQ

Limitations on Data Tank Queries from Presto

When creating or running Data Tank queries from Presto the following limitations apply:

• Lists of tables or table preview functions are not supported in the query editor. You can still explore tables and preview tables by issuing queries against Data Tank system tables and normal tables.
• You cannot access tables that use mixed case. Presto is a lowercase character system. Accessing case-sensitive systems like Postgres through Presto is a known issue.
• Presto doesn't support numeric types in data tanks. To use decimal data in data tanks with Presto, specify the "real" or "double precision" type.
• Queries from Presto against Data Tank tables that use aggregates (COUNT, MAX, SUM, etc.) may not generate optimal SQL for PostgreSQL. Presto does not push the aggregation down to the Data Tank PostgreSQL environment. Instead, all the rows needed to compute the aggregate are returned to Presto, and the aggregation is handled on a single node. If the source table is large enough, the required data movement and computation takes too long and TD may kill the query with a message like:
  Copy

  Copied

  [INTERNAL_FAILURE] Killed by the system because this query stalled for more than 1.00h. This is usually caused by a bug of Presto. The query execution will be retried in several minutes.Please ask support@treasure-data.com if you need a further help for this query

  To workaround the limitation, create a view in PostgreSQL that processes the aggregate on the PostgreSQL side, and query that view from the Presto side.

For example, a query against a 100M row Data Tank table giant_table is likely to fail:

select COUNT(*) as giant_count fromdatatank.public.giant_table;

A more efficient approach is to create a view in PostgreSQL that performs the resource-intensive processing in Data Tank:

create view count_view as select count(*) as cnt fromgiant_table;

Then execute a query on the view from Presto:

select cnt as giant_count from datatank.public.count_view;

Data Tank User Permissions for Presto Queries

When this querying functionality is turned on, Account Owner and Admin users are given Full Access permission.

All other users can be granted:

Granting Access for Data Tank Queries from Presto

The data tank database is only available if your account has had this feature turned on by request. Administrators can grant these access levels.

1. Make sure the Data Tank queries from Presto feature has been enabled for your account.
2. Log in to the TD Console as an Administrator.
3. Navigate to Settings > Team. For example, the Team section of TD Console.
4. Select the data tank database.
5. Specify the level of access you want to give.

Data Tank Query Statement Reference for Presto

Use Presto, via TD Console, to query data stored in your TD Data Tank.

This optional feature enables users of Data Tanks to run queries against their Data Tank instance directly from the Presto query engine. Contact your Customer Success representative or Support to learn more about adding this feature.

select avg(close) from datatank.public.normal_nasdaq where symbol = 'AAPL';
       _col0
--------------------
 14.823083442853807
(1 row)

-- sample_datasets.nasdaq is a TD table
-- sample_datasets. can be left out if you specify it at '-d' option in command-line or select it at the database dropdown list
select count(distinct time) 
  from datatank.public.normal_nasdaq dt_nasdaq JOIN sample_datasets.nasdaq td_nasdaq 
  ON dt_nasdaq.symbol = td_nasdaq.symbol AND dt_nasdaq.open = td_nasdaq.open AND dt_nasdaq.close = td_nasdaq.close 
where dt_nasdaq.symbol = 'AAPL';

insert into datatank.public.ms_nasdaq (symbol, open, volume, high, low, close, time) values ('AAPL', 10, 100, 20, 5, 7, td_scheduled_time());

insert into datatank.public.ms_nasdaq select * from sample_datasets.nasdaq where symbol = 'AAPL' ;

show schemas from datatank;
   Schema
--------------------
 information_schema
 pg_catalog
 public

show tables from datatank.public ;
     Table
---------------
 normal_nasdaq

desc datatank.public.normal_nasdaq;
 Column |  Type   | Comment
--------+---------+---------
 symbol | varchar |
 open   | double  |
 volume | bigint  |
 high   | double  |
 low    | double  |
 close  | double  |

create table datatank.public.ms_nasdaq as select * from sample_datasets.nasdaq where symbol = 'MSFT' ;

drop table datatank.public.ms_nasdaq;