Shops determine where releases are available. We have a standard set of shops, which will be available in the releases_simplified report, however, non-standard shops are not available there. By importing the shops data, you will be able to look up all shops for a release.

Base table

To create the table, we recommend the following structure:

CREATE TABLE IF NOT EXISTS RELEASE_SHOPS (
	RELEASE_ID VARCHAR,
	NAME VARCHAR,
	CONSTRAINT PKEY PRIMARY KEY (RELEASE_ID, NAME),
	CONSTRAINT FKEY_RELEASE_ID FOREIGN KEY (RELEASE_ID) REFERENCES RELEASES_SIMPLIFIED (RELEASE_ID) NOT ENFORCED
);

Importing data

This creates a stored procedure that atomically imports the data from the simplified releases report.

CREATE OR REPLACE PROCEDURE import_shops()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    -- Start a transaction
    BEGIN TRANSACTION;
    
    -- (Optional) Truncate or clean the table first, if desired:
    TRUNCATE TABLE RELEASE_SHOPS;
    
    COPY INTO RELEASE_SHOPS
    FROM @BEAT_STAGE/snapshots/beta/release_shops.csv.zst
    FILE_FORMAT = (FORMAT_NAME = 'BEAT_CSV_FORMAT')
    MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
    ON_ERROR = ABORT_STATEMENT;
    
    -- Commit the transaction
    COMMIT;
    
    RETURN 'Release shops imported successfully.';
END;
$$;

To import data once after you’ve created the stored procedure, you can do this:

CALL import_shops();

See the separate section for information on how to schedule nightly imports.