Am I reclustering this table correctly? Variant type

0
votes

I am trying to create a window function tells me if a table contains : Value A, B or A&B within a cluster

For example:

I have created a JSON partition with an s3 bucket like this: s3://bucket_name/application_one/2016/07/01/11/

Table content for your example from:

SELECT * from Test_cluster limit 2;

Row  V    T
1
{ "city": { "coord": { "lat": 2.2673, "lon": 102.5453 }, "country": "MY", "id": 1732857, "name": "Tangkak" }, "data": [ { "clouds": 100, "deg": 82, "dt": 1571544000, "humidity": 50, ...
2019-10-19 23:35:37.000

2
{ "city": { "coord": { "lat": 2.0251, "lon": 103.3328 }, "country": "MY", "id": 1732811, "name": "Kluang" }, "data": [ { "clouds": 100, "deg": 155, "dt": 1571544000, "humidity": 69, ...
2019-10-19 23:35:37.000

What I tried: 

ALTER  TABLE Test_Cluster CLUSTER BY (V, T);
ALTER  TABLE Test_Cluster RECLUSTER;

I am getting error that variant type is not supported, is there another format or cast I can use to recluster my variant Json table?

1
snowflake-cloud-data-platform

1 Answers

0
votes

Clustering on a variant is not supported, but you can cluster on a variant expression.

See the example below from the snowflake documentation:

-- cluster by paths in variant columns
create or replace table t3 (t timestamp, v variant) cluster by (v:"Data":id::number);