Querying data with ClickHouse Connect: Advanced usage

## External data[​](#external-data "Direct link to External data")

ClickHouse queries can accept external data in any ClickHouse format. This binary data is sent along with the query string to be used to process the data. Details of the External Data feature are [here](/docs/engines/table-engines/special/external-data). The client `query*` methods accept an optional `external_data` parameter to take advantage of this feature. The value for the `external_data` parameter should be a `clickhouse_connect.driver.external.ExternalData` object. The constructor for that object accepts the following arguments:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Name Type Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | file\_path str Path to a file on the local system path to read the external data from. Either `file_path` or `data` is required| file\_name str The name of the external data "file". If not provided, will be determined from the `file_path` (without extensions)| data bytes The external data in binary form (instead of being read from a file). Either `data` or `file_path` is required| fmt str The ClickHouse [Input Format](/docs/sql-reference/formats) of the data. Defaults to `TSV`| types str or seq of str A list of column data types in the external data. If a string, types should be separated by commas. Either `types` or `structure` is required| structure str or seq of str A list of column name + data type in the data (see examples). Either `structure` or `types` is required| mime\_type str Optional MIME type of the file data. Currently ClickHouse ignores this HTTP subheader | | | | | | | | | | | | | | | | | | | | | | | |

To send a query with an external CSV file containing "movie" data, and combine that data with an `directors` table already present on the ClickHouse server:

```
import clickhouse_connect
from clickhouse_connect.driver.external import ExternalData

client = clickhouse_connect.get_client()
ext_data = ExternalData(file_path='/data/movies.csv',
                        fmt='CSV',
                        structure=['movie String', 'year UInt16', 'rating Decimal32(3)', 'director String'])
result = client.query('SELECT name, avg(rating) FROM directors INNER JOIN movies ON directors.name = movies.director GROUP BY directors.name',
                      external_data=ext_data).result_rows
```

Additional external data files can be added to the initial `ExternalData` object using the `add_file` method, which takes the same parameters as the constructor. For HTTP, all external data is transmitted as part of a `multi-part/form-data` file upload.

## Time zones[​](#time-zones "Direct link to Time zones")

There are multiple mechanisms for applying a time zone to ClickHouse DateTime and DateTime64 values. Internally, the ClickHouse server always stores any DateTime or `DateTime64` object as a time zone naive number representing seconds since the epoch, 1970-01-01 00:00:00 UTC time. For `DateTime64` values, the representation can be milliseconds, microseconds, or nanoseconds since the epoch, depending on precision. As a result, the application of any time zone information always occurs on the client side. Note that this involves meaningful extra calculation, so in performance critical applications it is recommended to treat DateTime types as epoch timestamps except for user display and conversion (Pandas Timestamps, for example, are always a 64-bit integer representing epoch nanoseconds to improve performance).

When using time zone aware data types in queries - in particular the Python `datetime.datetime` object -- `clickhouse-connect` applies a client side time zone using the following precedence rules:

1. If the query method parameter `client_tzs` is specified for the query, the specific column time zone is applied
2. If the ClickHouse column has timezone metadata (i.e., it is a type like DateTime64(3, 'America/Denver')), the ClickHouse column timezone is applied. (Note this timezone metadata isn't available to clickhouse-connect for DateTime columns prior to ClickHouse version 23.2)
3. If the query method parameter `query_tz` is specified for the query, the "query timezone" is applied.
4. If a timezone setting is applied to the query or session, that timezone is applied. (This functionality isn't yet released in the ClickHouse server)
5. Finally, if the client `apply_server_timezone` parameter has been set to True (the default), the ClickHouse server timezone is applied.

Note that if the applied timezone based on these rules is UTC, `clickhouse-connect` will *always* return a time zone naive Python `datetime.datetime` object. Additional timezone information can then be added to this timezone naive object by the application code if desired.

- [QueryContexts](#querycontexts)- [Streaming queries](#streaming-queries)
    - [Data blocks](#data-blocks)- [HTTP data buffer for slow processing](#http-data-buffer-for-slow-processing)- [StreamContexts](#streamcontexts)- [Stream types](#stream-types)- [Streaming examples](#streaming-examples)- [NumPy, Pandas, and Arrow queries](#numpy-pandas-and-arrow-queries)
      - [NumPy queries](#numpy-queries)- [Pandas queries](#pandas-queries)- [PyArrow queries](#pyarrow-queries)- [Arrow-backed DataFrames](#arrow-backed-dataframes)- [Read formats](#read-formats)
        - [Read format options (Python types)](#read-format-options-python-types)- [External data](#external-data)- [Time zones](#time-zones)

Was this page helpful?