Actor data itself is simple, but it the relation to releases that makes it interesting. We therefore group the two versions into the same import statement here.

Actor information includes:

• Actor ID
• The actor’s name
• The date the actor was last modified
• The relationship between an actor and a release. An actor can be an author and a narrator for one release, but just a translator for a different release.

Base table

To create the table, we recommend the following structure:

CREATE TABLE IF NOT EXISTS ACTORS (
	ACTOR_ID VARCHAR,
	NAME VARCHAR(16777216),
	LAST_MOD TIMESTAMP_TZ(9),
	CONSTRAINT PKEY_ACTOR_ID PRIMARY KEY (ACTOR_ID)
);

CREATE TABLE IF NOT EXISTS RELEASE_ACTORS (
	RELEASE_ID VARCHAR NOT NULL,
	ACTOR_ID VARCHAR NOT NULL,
	ROLE VARCHAR NOT NULL,
	CONSTRAINT PKEY PRIMARY KEY (RELEASE_ID, ACTOR_ID, ROLE),
	CONSTRAINT FKEY_RELEASE_ID FOREIGN KEY (RELEASE_ID) REFERENCES RELEASES_SIMPLIFIED (RELEASE_ID) NOT ENFORCED,
	CONSTRAINT FKEY_ACTOR_ID FOREIGN KEY (ACTOR_ID) REFERENCES ACTORS (ACTOR_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_actors()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    -- Start a transaction
    BEGIN TRANSACTION;
    
    -- (Optional) Truncate or clean the table first, if desired:
    TRUNCATE TABLE ACTORS;
    TRUNCATE TABLE RELEASE_ACTORS;
    
    COPY INTO ACTORS
    FROM @BEAT_STAGE/snapshots/beta/actors.csv.zst
    FILE_FORMAT = (FORMAT_NAME = 'BEAT_CSV_FORMAT')
    MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
    ON_ERROR = ABORT_STATEMENT;
    
    COPY INTO RELEASE_ACTORS
    FROM @BEAT_STAGE/snapshots/beta/release_actors.csv.zst
    FILE_FORMAT = (FORMAT_NAME = 'BEAT_CSV_FORMAT')
    MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
    ON_ERROR = ABORT_STATEMENT;
    
    -- Commit the transaction
    COMMIT;
    
    RETURN 'Actors and relationships to releases imported successfully.';
END;
$$;

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

CALL import_actors();

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