Postgres with syntax error at or near "DISTKEY"


#1

Hi,

I am new for sqlrunner. When I try run sql-runner with playbook web-model.yml for PostgreSQLDB, I get this error:

snowplow#./sql-runner -playbook web-model.yml -softLock /locks/soft/1
2017/01/12 01:10:28 Checking and setting the lockfile at this key '/locks/soft/1'
2017/01/12 01:10:28 EXECUTING 00-web-page-context (in step 00-web-page-context @ PostgreSQLDB): /web-model/01-page-views/00-web-page-context.sql
2017/01/12 01:10:28 FAILURE: 00-web-page-context (step 00-web-page-context @ target PostgreSQLDB), ERROR: ERROR #42601 syntax error at or near "DISTKEY" (addr="127.0.0.1:5432")
2017/01/12 01:10:28 Deleting lockfile at this key '/locks/soft/1'
2017/01/12 01:10:28
TARGET INITIALIZATION FAILURES:
QUERY FAILURES:
* Query 00-web-page-context /web-model/01-page-views/00-web-page-context.sql (in step 00-web-page-context @ target PostgreSQLDB), ERROR:
  - ERROR #42601 syntax error at or near "DISTKEY" (addr="127.0.0.1:5432")

Anything wrong with this 00-web-page-context.sql script? Shoud I remove the DISTKEY line? Or where I can find script for PostgreSQLDB?

Thanks for any help.


#2

The SQL scripts in that directory web-model/redshift/sql are specifically for Redshift so some features in the DDLs like DISTKEYs and SORTKEYs won’t exist. You’ll be able to run them by modifying some of these scripts however because they’ve been designed for Redshift (a columnar database) they are unlikely to be as performant on Postgres (row oriented).


#3

Thanks for reply

Can I find Postgres playbook script at somewhere? Or no playbook script for Postgres at all?

I find this (https://github.com/snowplow/sql-runner/blob/master/integration/resources/good-postgres.yml) at github, but I dont think its the playbook for Postgres (dont know how to use it).

Very appreciate for help


#4

There are two directories (excluding looker):
https://github.com/snowplow/snowplow/tree/master/5-data-modeling/web-model/sql-runner
https://github.com/snowplow/snowplow/tree/master/5-data-modeling/web-model/redshift

Can you please confirm that both are intended to work with redshift (i.e. neither will work with Postgresql)?