title_wip

### s3() function and S3 Table named collection examples[​](#s3-function-and-s3-table-named-collection-examples "Direct link to s3() function and S3 Table named collection examples")

Both of the following examples use the same named collection `s3_mydata`:

#### s3() function[​](#s3-function "Direct link to s3() function")

```
INSERT INTO FUNCTION s3(s3_mydata, filename = 'test_file.tsv.gz',
   format = 'TSV', structure = 'number UInt64', compression_method = 'gzip')
SELECT * FROM numbers(10000);
```

Tip

The first argument to the `s3()` function above is the name of the collection, `s3_mydata`. Without named collections, the access key ID, secret, format, and URL would all be passed in every call to the `s3()` function.

#### S3 table[​](#s3-table "Direct link to S3 table")

```
CREATE TABLE s3_engine_table (number Int64)
ENGINE=S3(s3_mydata, url='https://s3.us-east-1.amazonaws.com/yourbucket/mydata/test_file.tsv.gz', format = 'TSV')
SETTINGS input_format_with_names_use_header = 0;

SELECT * FROM s3_engine_table LIMIT 3;
┌─number─┐
│      0 │
│      1 │
│      2 │
└────────┘
```

## Named collections for accessing MySQL database[​](#named-collections-for-accessing-mysql-database "Direct link to Named collections for accessing MySQL database")

The description of parameters see [mysql](/docs/sql-reference/table-functions/mysql).

### DDL example[​](#ddl-example-2 "Direct link to DDL example")

```
CREATE NAMED COLLECTION mymysql AS
user = 'myuser',
password = 'mypass',
host = '127.0.0.1',
port = 3306,
database = 'test',
connection_pool_size = 8,
replace_query = 1
```

### XML example[​](#xml-example-2 "Direct link to XML example")

```
<clickhouse>
    <named_collections>
        <mymysql>
            <user>myuser</user>
            <password>mypass</password>
            <host>127.0.0.1</host>
            <port>3306</port>
            <database>test</database>
            <connection_pool_size>8</connection_pool_size>
            <replace_query>1</replace_query>
        </mymysql>
    </named_collections>
</clickhouse>
```

### mysql() function, MySQL table, MySQL database, and Dictionary named collection examples[​](#mysql-function-mysql-table-mysql-database-and-dictionary-named-collection-examples "Direct link to mysql() function, MySQL table, MySQL database, and Dictionary named collection examples")

The four following examples use the same named collection `mymysql`:

#### mysql() function[​](#mysql-function "Direct link to mysql() function")

```
SELECT count() FROM mysql(mymysql, table = 'test');

┌─count()─┐
│       3 │
└─────────┘
```

Note

The named collection does not specify the `table` parameter, so it is specified in the function call as `table = 'test'`.

#### MySQL table[​](#mysql-table "Direct link to MySQL table")

```
CREATE TABLE mytable(A Int64) ENGINE = MySQL(mymysql, table = 'test', connection_pool_size=3, replace_query=0);
SELECT count() FROM mytable;

┌─count()─┐
│       3 │
└─────────┘
```

Note

The DDL overrides the named collection setting for connection\_pool\_size.

#### MySQL database[​](#mysql-database "Direct link to MySQL database")

```
CREATE DATABASE mydatabase ENGINE = MySQL(mymysql);

SHOW TABLES FROM mydatabase;

┌─name───┐
│ source │
│ test   │
└────────┘
```

#### MySQL Dictionary[​](#mysql-dictionary "Direct link to MySQL Dictionary")

```
CREATE DICTIONARY dict (A Int64, B String)
PRIMARY KEY A
SOURCE(MYSQL(NAME mymysql TABLE 'source'))
LIFETIME(MIN 1 MAX 2)
LAYOUT(HASHED());

SELECT dictGet('dict', 'B', 2);

┌─dictGet('dict', 'B', 2)─┐
│ two                     │
└─────────────────────────┘
```

## Named collections for accessing PostgreSQL database[​](#named-collections-for-accessing-postgresql-database "Direct link to Named collections for accessing PostgreSQL database")

The description of parameters see [postgresql](/docs/sql-reference/table-functions/postgresql). Additionally, there are aliases:

- `username` for `user`
- `db` for `database`.

Parameter `addresses_expr` is used in a collection instead of `host:port`. The parameter is optional, because there are other optional ones: `host`, `hostname`, `port`. The following pseudo code explains the priority:

```
CASE
    WHEN collection['addresses_expr'] != '' THEN collection['addresses_expr']
    WHEN collection['host'] != ''           THEN collection['host'] || ':' || if(collection['port'] != '', collection['port'], '5432')
    WHEN collection['hostname'] != ''       THEN collection['hostname'] || ':' || if(collection['port'] != '', collection['port'], '5432')
END
```

Example of creation:

```
CREATE NAMED COLLECTION mypg AS
user = 'pguser',
password = 'jw8s0F4',
host = '127.0.0.1',
port = 5432,
database = 'test',
schema = 'test_schema'
```

Example of configuration:

```
<clickhouse>
    <named_collections>
        <mypg>
            <user>pguser</user>
            <password>jw8s0F4</password>
            <host>127.0.0.1</host>
            <port>5432</port>
            <database>test</database>
            <schema>test_schema</schema>
        </mypg>
    </named_collections>
</clickhouse>
```

### Example of using named collections with the postgresql function[​](#example-of-using-named-collections-with-the-postgresql-function "Direct link to Example of using named collections with the postgresql function")

```
SELECT * FROM postgresql(mypg, table = 'test');

┌─a─┬─b───┐
│ 2 │ two │
│ 1 │ one │
└───┴─────┘
SELECT * FROM postgresql(mypg, table = 'test', schema = 'public');

┌─a─┐
│ 1 │
│ 2 │
│ 3 │
└───┘
```

### Example of using named collections with database with engine PostgreSQL[​](#example-of-using-named-collections-with-database-with-engine-postgresql "Direct link to Example of using named collections with database with engine PostgreSQL")

```
CREATE TABLE mypgtable (a Int64) ENGINE = PostgreSQL(mypg, table = 'test', schema = 'public');

SELECT * FROM mypgtable;

┌─a─┐
│ 1 │
│ 2 │
│ 3 │
└───┘
```

Note

PostgreSQL copies data from the named collection when the table is being created. A change in the collection does not affect the existing tables.

### Example of using named collections with database with engine PostgreSQL[​](#example-of-using-named-collections-with-database-with-engine-postgresql-1 "Direct link to Example of using named collections with database with engine PostgreSQL")

```
CREATE DATABASE mydatabase ENGINE = PostgreSQL(mypg);

SHOW TABLES FROM mydatabase

┌─name─┐
│ test │
└──────┘
```

### Example of using named collections with a dictionary with source POSTGRESQL[​](#example-of-using-named-collections-with-a-dictionary-with-source-postgresql "Direct link to Example of using named collections with a dictionary with source POSTGRESQL")

```
CREATE DICTIONARY dict (a Int64, b String)
PRIMARY KEY a
SOURCE(POSTGRESQL(NAME mypg TABLE test))
LIFETIME(MIN 1 MAX 2)
LAYOUT(HASHED());

SELECT dictGet('dict', 'b', 2);

┌─dictGet('dict', 'b', 2)─┐
│ two                     │
└─────────────────────────┘
```

## Named collections for accessing a remote ClickHouse database[​](#named-collections-for-accessing-a-remote-clickhouse-database "Direct link to Named collections for accessing a remote ClickHouse database")

The description of parameters see [remote](/docs/sql-reference/table-functions/remote#parameters).

Example of configuration:

```
CREATE NAMED COLLECTION remote1 AS
host = 'remote_host',
port = 9000,
database = 'system',
user = 'foo',
password = 'secret',
secure = 1
```

```
<clickhouse>
    <named_collections>
        <remote1>
            <host>remote_host</host>
            <port>9000</port>
            <database>system</database>
            <user>foo</user>
            <password>secret</password>
            <secure>1</secure>
        </remote1>
    </named_collections>
</clickhouse>
```

`secure` is not needed for connection because of `remoteSecure`, but it can be used for dictionaries.

### Example of using named collections with the `remote`/`remoteSecure` functions[​](#example-of-using-named-collections-with-the-remoteremotesecure-functions "Direct link to example-of-using-named-collections-with-the-remoteremotesecure-functions")