When doing your analysis in Workspaces, if you are unfamiliar with the software available, there are some tips that may help with your analysis.

In this article, you will learn about:

Prerequisites:

  • A Workspace is running with an active Data License

  • Tables from the Manage Data section in the Data Republic platform have been loaded to the Workspace

Optimising tables in SQL Workbench

Every database has particular nuances with performance, and for optimal performance, Redshift has some details that should be included when creating tables.

The key details to add are:

  • DISTKEY and SORTKEY fields to help optimise the table when querying from the table

  • Datatypes - by reducing the amount of characters needed for strings, you can reduce the computation needed for each field.

  • Using VACUUM to clean up the table and any unused space in the database.

DROP TABLE IF EXISTS trans_optim; CREATE TABLE trans_optim ( 
transaction_id VARCHAR(6),
cust_id VARCHAR(6),
transaction_amount VARCHAR(5),
transaction_time DATETIME(11),
mcc VARCHAR(12),
merchant_sa2 VARCHAR(9),
online_merch VARCHAR(1) )
DISTKEY (merchant_id)
SORTKEY (merchant_id, mcc, merchant_sa2);

INSERT INTO trans_optim (
SELECT DISTINCT transaction_id::VARCHAR(6),
cust_id::VARCHAR(6),
transaction_amount ::VARCHAR(5),
transaction_time::DATETIME(11),
mcc::VARCHAR(12),
merchant_sa2::VARCHAR(9),
online_merch::VARCHAR(1)
FROM trans_data);

VACUUM trans_optim;

Do your analysis

Did this answer your question?