Help Center/ CloudTable Service/ Developer Guide/ ClickHouse Application Development Guide/ Data Migration and Synchronization/ Importing and Exporting data
Updated on 2024-04-29 GMT+08:00
View PDF
Share

Importing and Exporting data

This section describes the basic syntax and usage of the SQL statements for importing and exporting file data using ClickHouse.

Importing and Exporting Data in CSV Format

  • Import data in CSV format.
    • For normal clusters: 
      cat csv_ssl | ./clickhouse client --host 192.168.x.x --port port --user admin --password password --database test010 --query="INSERT INTO test145 FORMAT CSV"
    • For security clusters: 
      cat csv_no_ssl | ./clickhouse client --host 192.168.x.x --port port --user admin --password password --config-file ./config.xml --database test010 --query="INSERT INTO test146 FORMAT CSV"
  1. host: indicates the host name or ClickHouse instance IP address.
  2. port: indicates the port number (available on the cluster details page).
  3. user: indicates the username created during cluster creation.
  4. database: indicates the database name.
  5. password: indicates the password created during cluster creation.
  6. INSERT INTO: Enter the target data table behind this parameter.
  7. cat File path: indicates the file storage path, which can be customized.
  8. config-file ./config.xml: indicates the configuration file. For details, see ClickHouse Secure Channel.
  • Export data in CSV format.
    • For normal clusters: 
      ./clickhouse client --host 192.168.x.x --port port --user admin --password Password --database test010 -m --query="select * from test139 FORMAT CSV" > ./csv_no_ssl
    • For security clusters: 
      ./clickhouse client --host 192.168.x.x --port port --user admin --password password --config-file ./config.xml --database test010 -m --query="select * from test139 FORMAT CSV" > ./csv_no_ssl
  1. host: indicates the host name or ClickHouse instance IP address.
  2. port: indicates the port number (available on the cluster details page).
  3. user: indicates the username created during cluster creation.
  4. database: indicates the database name.
  5. password: indicates the password created during cluster creation.
  6. SELECT * FROM: Enter the target data table behind this parameter.
  7. ./csv_no_ssl: indicates the file storage path, which can be customized.
  8. config-file ./config.xml: indicates the configuration file. For details, see ClickHouse Secure Channel.

Importing and Exporting Data in Parquet Format

  • Import data in Parquet format.
    • For normal clusters: 
      cat parquet_no_ssl.parquet | ./clickhouse client --host 192.168.x.x --port port --user admin --password password --database test010 --query="INSERT INTO test145 FORMAT Parquet"
    • For security clusters: 
      cat parquet_no_ssl.parquet | ./clickhouse client --host 192.168.x.x --port port --user admin --password password --config-file ./config.xml --database test010 --query="INSERT INTO test146 FORMAT Parquet"
  1. parquet_no_ssl.parquet: indicates the path for storing the files, which can be customized.
  2. host: indicates the host name or ClickHouse instance IP address.
  3. port: indicates the port number (available on the cluster details page).
  4. user: indicates the username created during cluster creation.
  5. database: indicates the database name.
  6. password: indicates the password created during cluster creation.
  7. INSERT INTO: Enter the target data table behind this parameter.
  8. config-file ./config.xml: indicates the configuration file. For details, see ClickHouse Secure Channel.
  • Export data in Parquet format.
    • For normal clusters: 
      ./clickhouse client --host 192.168.x.x --port port --user admin --password password --database test010 -m --query="select * from test139 FORMAT Parquet" > ./parquet_no_ssl.parquet
    • For security clusters: 
      ./clickhouse client --host 192.168.x.x --port port --user admin --password password --config-file ./config.xml --database test010 -m --query="select * from test139 FORMAT Parquet" > ./parquet_ssl.parque
  1. host: indicates the host name or ClickHouse instance IP address.
  2. port: indicates the port number (available on the cluster details page).
  3. user: indicates the username created during cluster creation.
  4. database: indicates the database name.
  5. password: indicates the password created during cluster creation.
  6. select * from: Enter the target data table behind this parameter.
  7. ./parquet_no_ssl.parquet: indicates the path for storing the exported Parquet files, which can be customized.
  8. config-file ./config.xml: indicates the configuration file. For details, see ClickHouse Secure Channel.

Importing and Exporting Data in ORC Format

  • Import data in ORC format.
    • For normal clusters: 
      cat orc_no_ssl.orc | ./clickhouse client --host 192.168.x.x --port port --user admin --password password --database test010 --query="INSERT INTO test143 FORMAT ORC"
    • For security clusters: 
      cat orc_no_ssl.orc | ./clickhouse client --host 192.168.x.x --port port --user admin --password password --config-file ./config.xml --database test010 --query="INSERT INTO test144 FORMAT ORC
  1. cat orc_no_ssl.orc: path for storing the ORC files, which can be customized.
  2. host: indicates the host name or ClickHouse instance IP address.
  3. port: indicates the port number (available on the cluster details page).
  4. user: indicates the username created during cluster creation.
  5. database: indicates the database name.
  6. password: indicates the password created during cluster creation.
  7. INSERT INTO: Enter the target data table behind this parameter.
  8. config-file ./config.xml: indicates the configuration file. For details, see ClickHouse Secure Channel.
  • Export data in ORC format.
    • For security clusters: 
      ./clickhouse client --host 192.168.x.x --port port --user admin --password password --config-file ./config.xml --database test010 -m --query="select * from test139 FORMAT ORC" > ./orc_ssl.orc
    • For normal clusters: 
      ./clickhouse client --host 192.168.x.x --port port --user admin --password password --database test010 -m --query="select * from test139 FORMAT ORC" > ./orc_no_ssl.orc
  1. host: indicates the host name or ClickHouse instance IP address.
  2. port: indicates the port number (available on the cluster details page).
  3. user: indicates the username created during cluster creation.
  4. database: indicates the database name.
  5. password: indicates the password created during cluster creation.
  6. config-file ./config.xml: indicates the configuration file. For details, see ClickHouse Secure Channel.
  7. select * from: Enter the target data table behind this parameter.
  8. /opt/student.orc: path for storing the exported ORC file, which can be customized.

Importing and Exporting Data in JSON Format

  • Import data in JSON format.
    • For normal clusters: 
      cat ./jsonnossl.json | ./clickhouse client --host 192.168.x.x --port port --user admin --password password --database test010 --query="INSERT INTO test141 FORMAT JSON"
    • For security clusters: 
      cat ./jsonssl.json | ./clickhouse client --host 192.168.x.x --port port --user admin --password password --config-file ./config.xml --database test010 --query="INSERT INTO test142 FORMAT JSON"
  1. cat File path: indicates the source path, which can be customized.
  2. host: indicates the host name or ClickHouse instance IP address.
  3. port: indicates the port number (available on the cluster details page).
  4. user: indicates the username created during cluster creation.
  5. database: indicates the database name.
  6. password: indicates the password created during cluster creation.
  7. INSERT INTO: Enter the target data table behind this parameter.
  8. config-file ./config.xml: indicates the configuration file. For details, see ClickHouse Secure Channel.
  • Export data in JSON format.
    • For security clusters: 
      ./clickhouse client --host 192.168.x.x --port port --user admin --password password --database test010 -m --query="select * from test139 FORMAT JSON" > ./jsonnossl.json
    • For normal clusters: 
      ./clickhouse client --host 192.168.x.x --port port --user admin --password password --config-file ./config.xml --database test010 -m --query="select * from test139 FORMAT JSON" > ./jsonssl.json
  1. host: indicates the host name or ClickHouse instance IP address.
  2. port: indicates the port number (available on the cluster details page).
  3. user: indicates the username created during cluster creation.
  4. database: indicates the database name.
  5. password: indicates the password created during cluster creation.
  6. SELECT * FROM: Enter the target data table behind this parameter.
  7. ./jsonssl.json: indicates the destination path, which can be customized.
  8. config-file ./config.xml: indicates the configuration file. For details, see ClickHouse Secure Channel.