uBXS5cy4

1## Creating an external table using Spectrum and Redshift
2Create the external schema for the Redshift Spectrum data on S3.  The only way to get external tables in Redshift is via either Athena's or Hive's metastore table, so you have to create a db for Athena below in "data catalog".  Note that you should create a schema for the external table first.
3```
4drop schema if exists history_schema;
5
6create external schema if not exists history_schema 
7from data catalog -- athena metastore hook
8database 'mysql-export-dev'
9region 'us-west-2'
10iam_role 'arn:aws:iam::<account>:role/RedshiftCopyUnload'
11create external database if not exists;
12
13# useful debug query for internal redshift table setup...
14select esoid, nspname as schemaname, nspowner, esdbname as external_db, esoptions 
15from pg_namespace a,pg_external_schema b where a.oid=b.esoid;
16
17select * from svv_external_tables;
18
19Create table:
20create external table history_schema.history (
21  historyId VARCHAR, 
22  createdTimestamp VARCHAR, 
23  deviceId VARCHAR, 
24  deviceProfileId VARCHAR, 
25  deviceTypeId VARCHAR, 
26  partnerId VARCHAR, 
27  theType VARCHAR, 
28  accountId VARCHAR, 
29  attributeId VARCHAR, 
30  attributeValueRaw VARCHAR, 
31  attributeDataType VARCHAR, 
32  attributeValue DECIMAL, 
33  presentationId VARCHAR, 
34  lat DOUBLE PRECISION, 
35  lon DOUBLE PRECISION
36)
37row format delimited
38fields terminated by ','
39stored as textfile
40location 's3://mysql-export-dev/2017-07-11/';
41```
42### Partitioned External Tables
43You might have data that is streamed into s3 periodically, say from a nightly summary job.  Here's how to update an existing external table using partitions (this one by date).
44```
45CREATE EXTERNAL TABLE s3.history (
46    attributeId integer,
47    summaryName varchar,
48    deviceId varchar,
49    summarizedValue varchar,
50    epochStart bigint,
51    epochEnd bigint,
52    counter bigint
53)
54partitioned by (day date)
55stored as PARQUET
56location 's3://redshift-parquet-history/';
57
58alter table s3.history
59add partition(saledate='2017-07-08') 
60location 's3://my-redshift-history/day=2017-07-08/';
61
62alter table s3.history
63add partition(saledate='2017-07-09') 
64location 's3://my-redshift-history/day=2017-07-09/';
65
66alter table s3.history
67add partition(saledate='2017-07-10') 
68location 's3://my-redshift-history/day=2017-07-09/';
69
70```
71### Check any Redshift load errors
72```
73
74select starttime, err_reason, line_number,
75  colname, type, col_length, position, raw_field_value,
76  raw_line, err_code, filename 
77from stl_load_errors
78order by starttime desc
79```