Duckdb as portable catalog

ducky_catalog.sql

-- ataching the remote database (268KB)
ATTACH 's3://us-prd-motherduck-open-datasets/content/duckdb-as-catalog/ducky_catalog.ddb';

SHOW all tables;

-- queries the data
FROM ducky_catalog.customers limit 5;

-- Listing views
SELECT sql FROM duckdb_views() where temporary=false;

-- Example on how to create a secrets based on sso AWS creds
CREATE SECRET secret (
      TYPE S3,
      PROVIDER CREDENTIAL_CHAIN,
      CHAIN 'sso'
  );

-- Example on how to create a view based on Postgres table using env variable
CREATE VIEW ducks AS
SELECT * FROM postgres_scan(
    'dbname=' || getenv('PGDATABASE') || 
    ' host=' || getenv('PGHOST') || 
    ' user=' || getenv('PGUSER') || 
    ' password=' || getenv('PGPASSWORD') || 
    ' connect_timeout=10 sslmode=require',
    'public', 
    'ducks'
);

-- connecting to MotherDuck and uploading your local db
ATTACH 'md:'
CREATE DATABASE cloud_ducky_catalog from ducky_catalog;

-- creating a MotherDuck share
CREATE SHARE share_ducky_catalog from cloud_ducky_catalog3 (ACCESS UNRESTRICTED, VISIBILITY HIDDEN, UPDATE AUTOMATIC);