This example uses a CREATE TABLE...AS with a SELECT subquery to copy data from a foreign table into a permanent table without a primary index.
Internally, the SELECT subquery performs an INSERT...SELECT operation with the HASH BY RANDOM clause to provide even distribution of data. The HASH BY RANDOM clause is the default for an INSERT...SELECT operation into a permanent table without a primary index from a foreign table. You can disable this behavior. See Disable HASH BY RANDOM.
Below is the foreign table definition of riverflow_0627 for this example. The JSON payload column is created by the system automatically as it detects the JSON data type.
The DefAuth_S3 authorization object was created in Example: Specifying a Definer Authorization for a Foreign Table.
CREATE FOREIGN TABLE riverflow_0627, EXTERNAL SECURITY DEFINER TRUSTED DefAuth_S3 USING ( LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json') ) ;
This statement defines the permanent table riverdata_0627_perm with a subquery that selects the location and payload columns from the foreign table riverflow_0627, including the data.
CREATE TABLE riverdata_0627_perm AS (SELECT location, payload FROM riverflow_0627) WITH DATA NO PRIMARY INDEX;