DISTRIBUTE BY notices in Greenplum

sql greenplum
Amelio Vazquez-Reina picture Amelio Vazquez-Reina · Feb 7, 2014 · Viewed 15.7k times · Source

Say I run the following query on psql:

> select a.c1, b.c2 into temp_table from db.A as a inner join db.B as b 
> on a.x = b.x limit 10;

I get the following message:

NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'c1' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.

  1. What is a DISTRIBUTED BY column?
  2. Where is temp_table stored? Is it stored on my client or on the server?

Answer

Wes Reing picture Wes Reing · Feb 7, 2014

  1. DISTRIBUTED BY is how Greenplum determines which segment will store each row. Because Greenplum is an MPP database in most production databases you will have multiple segment servers. You want to make sure that the Distribution column is the column you will join on usaly.

  2. temp_table is a table that will be created for you on the Greenplum cluster. If you haven't set search_path to something else it will be in the public schema.

Related questions

Greenplum vs PostgreSQL

What are the arguments for and against using Greenplum instead of PostgreSQL in a webapp (django) environment? My gut reaction is to prefer PostgreSQL's open-source approach and huge knowledgebase. My configuration (though I'd love to hear about any other configuration) …

Read More
How to use a SQL window function to calculate a percentage of an aggregate

I need to calculate percentages of various dimensions in a table. I'd like to simplify things by using window functions to calculate the denominator, however I am having an issue because the numerator has to be an aggregate as well. …

Read More
Where clause inside an over clause in postgres

Is it possible to use the where clause inside an overclause as below ? SELECT SUM(amount) OVER(partition by prod_name WHERE dateval > dateval_13week) I cannot use preceding and following inside over clause as my dates are not …

Read More