Why can I not refresh my Snowflake External Table?-CodePudding

I am getting the message "query produced no results" when I try to execute a refresh of my external table in Snowflake. Below are the steps I have taken leading up to the final statement that is not executing successfully.

When I execute the following, I can successfully see the .json file I am trying to access in my stage (azure blob storage):

LIST @AZURE_STAGE_ECL_RAW

I have created a file format as follows:

CREATE OR REPLACE FILE FORMAT ECL_JSON_FORMAT
    TYPE = JSON
    FILE_EXTENSION = '.json';

I have ensured my .json file is of ndjson format as follows:

{"code": "value1", "display": "value2", "version": "value3", "cluster_name": "value4"}
{"code": "value1", "display": "value2", "version": "value3", "cluster_name": "value4"}
{"code": "value1", "display": "value2", "version": "value3", "cluster_name": "value4"}
{"code": "value1", "display": "value2", "version": "value3", "cluster_name": "value4"}

I created my external table as follows:

CREATE OR REPLACE EXTERNAL TABLE CLUSTERS_SNOMED_ECL
(
    Code TEXT AS (value:"code"::TEXT)
    ,Display TEXT AS (value:"display"::TEXT)
    ,Version TEXT AS (value:"version"::TEXT)
    ,Cluster_Name TEXT AS (value:"cluster_name"::TEXT)
)
WITH LOCATION = @AZURE_STAGE_ECL_RAW/clusters.json
FILE_FORMAT = (FORMAT_NAME = ECL_JSON_FORMAT);

When I execute the following I can see the created columns but no data as per the attached picture:

SELECT * FROM CLUSTERS_SNOMED_ECL

So finally I am trying to refresh my external table for the inaugural time however when I execute the following, I am returned the message "query produced no results":

ALTER EXTERNAL TABLE CLUSTERS_SNOMED_ECL REFRESH;

Can anybody suggest what I should do next to successfully refresh and pull the data into my table?

CodePudding user response：

Check if you can list the Metadata of the file from the stage using the command select metadata$filename from @ZURE_STAGE_ECL_RAW;

If you get an access deny message then there is a permission issue at the file level. ls @ZURE_STAGE_ECL_RAW, can work, if you have the permission on the Blob, but you need to have the right permission on the File as well.

CodePudding user response：

The file format does not need the FILE_EXTENSION part:

CREATE OR REPLACE FILE FORMAT ECL_JSON_FORMAT
    TYPE = JSON
    -- FILE_EXTENSION = '.json';

CREATE FILE FORMAT:

FILE_EXTENSION = 'string' | NONE

Use

Data unloading only

Definition

Specifies the extension for files unloaded to a stage. Accepts any extension. The user is responsible for specifying a file extension that can be read by any desired software or services.

The table definition could use FORMAT_TYPE instead:

CREATE OR REPLACE EXTERNAL TABLE CLUSTERS_SNOMED_ECL
(
    Code TEXT AS (value:"code"::TEXT)
    ,Display TEXT AS (value:"display"::TEXT)
    ,Version TEXT AS (value:"version"::TEXT)
    ,Cluster_Name TEXT AS (value:"cluster_name"::TEXT)
)
WITH LOCATION = @AZURE_STAGE_ECL_RAW/clusters.json
FILE_FORMAT = (FORMAT_TYPE = JSON);