# Connecting to Data Sources

Shaper can query any data source DuckDB supports - including any extensions.

Use Shaper to attach to your production Postgres database, query files on S3 or even query a Google Sheet directly.

To query remote data sources, you will need to configure secrets, attach databases and load extensions.
See below for how to do that with Shaper's *init-sql* functionality.

Since DuckDB is also a database, you can also store data within Shaper.
- Shaper has an [Ingest API](/shaper/docs/ingesting-data-into-shaper/) to ingest data via an HTTP API.
- You can also use [scheduled jobs](/shaper/docs/tasks-and-scheduling/) to routinely load and transform data.

## Querying Data

The easiest way to query external data is through files.

DuckDB can read local files and it can also read files via HTTP or from S3 and other object storages directly.

Common file formats are supported out of the box:
- **CSV**
- **JSON**
- **Parquet**
- **Text files**
- **Excel**

You can find the complete list in the [DuckDB documentation](https://duckdb.org/docs/stable/data/data_sources).

In addition to files, you can also attach to databases such as [Postgres](https://duckdb.org/docs/stable/core_extensions/postgres), [MySQL](https://duckdb.org/docs/stable/core_extensions/mysql) and [Iceberg](https://duckdb.org/docs/stable/core_extensions/iceberg/overview) data lakes. For more see [below](#connecting-to-remote-databases).


## init-sql

Specify SQL that is executed when the system is started using the `--init-sql` flag or
use `--init-sql-file` to specify a file that contains SQL.

Use init-sql to:
- Create tables and views
- Manage credentials
- Connect to remote databases
- Load extensions


### Credential Management

To access data that requires credentials, you can use [DuckDB Secrets](https://duckdb.org/docs/stable/configuration/secrets_manager) together with Shaper's *init-sql* functionality.

Environment variables specified as `$VAR` or `${VAR}` are substituted in the SQL.

For example, you can set up a DuckDB secret for S3 credentials like this:
```sql
CREATE OR REPLACE SECRET mys3 (
    TYPE s3,
    KEY_ID '${S3_KEY_ID}',
    SECRET '${S3_SECRET}',
    REGION 'my_region',
    SCOPE 's3://my_bucket'
);
```

### Connecting to Remote Databases

DuckDB allows you to attach to remote databases.

There are extensions to attach to many common databases:
- Postgres
- SQLite
- MySQL
- Another DuckDB database file

Attach to a database by using [DuckDB's ATTACH functionality](https://duckdb.org/docs/stable/sql/statements/attach) together with Shaper's init-sql functionality.

Specify SQL that is executed when the system is started using the [`--init-sql`](/shaper/docs/configuration-options/#init-sql) flag.
You can also use [`--init-sql-file`](/shaper/docs/configuration-options/#init-sql-file) to specify a file that contains SQL.

For example, you can connect to a local Postgres database:
```sql
ATTACH IF NOT EXISTS 'postgresql://postgres@127.0.0.1/mydb' AS mydb (TYPE POSTGRES, READ_ONLY);
```

Use environment variables to pass the connection strings and secrets in production:
```sql
ATTACH IF NOT EXISTS '${DATABASE_URL}' AS mydb (TYPE POSTGRES, READ_ONLY);
```


### DuckDB Extensions

Make use of any DuckDB extension within Shaper.

There are extensions to do HTTP calls and query data sources from [Snowflake](https://duckdb.org/community_extensions/extensions/snowflake.html), [Big Query](https://duckdb.org/community_extensions/extensions/bigquery.html) and [Clickhouse](https://duckdb.org/community_extensions/extensions/chsql_native.html) to [Arrow Flight](https://airport.query.farm/) and [Google Sheets](https://duckdb-gsheets.com/).

Learn about all existing core and community extensions in the [DuckDB documentation](https://duckdb.org/docs/stable/extensions/overview).

If you run into any issues with extensions or have any questions, don't hesitate to reach out.

The many extensions are installed and loaded automatically when they are first used.

Otherwise you can use `INSTALL` and `LOAD` with Shaper's init-sql functionality (see above).

Change the directory extensions are installed in using the `--duckdb-ext-dir` flag.