2

We created the schema as follows:

create external schema spectrum
from data catalog
database 'test'
iam_role 'arn:aws:iam::20XXXXXXXXXXX:role/athenaaccess'
create external database if not exists;

and table as follows:

create external table spectrum.Customer(
Subr_Id integer,
SUB_CURRENTSTATUS varchar(100),
AIN integer,
ACCOUNT_CREATED timestamp,
Subr_Name varchar(100),
LAST_DEACTIVATED timestamp)
partitioned by (LAST_ACTIVATION timestamp)
row format delimited
fields terminated by ','
stored as textfile
location 's3://cequity-redshiftspectrum-test/'
table properties ('numRows'='1000');

the access rights are as follows:

Roles of athenaQuickSight access, Full Athena access, and s3 full access are attached to the redshift cluster

However, when we query as below we are getting 0 records. please help.

select count(*) from spectrum.Customer;
Vzzarr
  • 4,600
  • 2
  • 43
  • 80
Harshinee.R
  • 21
  • 3

1 Answers1

2

If your query returns zero rows from a partitioned external table, check whether a partition has been added to this external table. Redshift Spectrum only scans files in an Amazon S3 location that has been explicitly added using ALTER TABLE … ADD PARTITION. Query the SVV_EXTERNAL_PARTITIONS view to finding existing partitions. Run ALTER TABLE ADD … PARTITION for each missing partition.

Reference

I had the same issue. Doing the above, resolved my issue.

P.S. Explicit run of ALTER TABLE command to create partition can also be automated.

DJo
  • 2,133
  • 4
  • 30
  • 46
Sumit Saurabh
  • 1,366
  • 1
  • 19
  • 33
  • Great works well. But how can you keep on adding new partitions to the same table in an automated way? – Rohan Chitambar Nov 10 '17 at 07:41
  • Please follow this link. https://stackoverflow.com/questions/47184577/redshift-spectrum-automatically-partition-tables-by-date-folder/47217546#47217546 – Sumit Saurabh Nov 10 '17 at 07:42