CODEWITHSUNDEEP
amazon-web-servicesamazon-redshiftamazon-redshift-spectrum
Harshinee.R
Harshinee.R

Reputation: 21

Getting 0 rows while querying external table in redshift

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;

Upvotes: 2

Views: 2501

Answers (1)

Sumit Saurabh
Sumit Saurabh

Reputation: 1446

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.

Upvotes: 3

Related Questions