Importing and Exporting data

Importing and Exporting Data in CSV Format

• Import data in CSV format.
  • Non-security cluster

    cat csv_ssl | ./clickhouse client --host 192.168.x.x --port port --user admin --password password --database test010 --query="INSERT INTO test145 FORMAT CSV"

  • Security cluster

    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 specified during cluster creation.
6. INSERT INTO: indicates the target data table.
7. cat File path: indicates the user-defined path for storing the file.
8. config-file ./config.xml: indicates the configuration file. For details, see Using a Client to Connect to a ClickHouse Security Cluster.

• Export data in CSV format.
  • Non-security cluster

    ./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

  • Security cluster

    ./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 specified during cluster creation.
6. SELECT * FROM: indicates the target data table.
7. ./csv_no_ssl: indicates the user-defined path for storing the file.
8. config-file ./config.xml: indicates the configuration file. For details, see Using a Client to Connect to a ClickHouse Security Cluster.

Importing and Exporting Data in Parquet Format

• Import data in Parquet format.
  • Non-security cluster

    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"

  • Security cluster

    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 user-defined path for storing the file.
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 specified during cluster creation.
7. INSERT INTO: indicates the target data table.
8. config-file ./config.xml: indicates the configuration file. For details, see Using a Client to Connect to a ClickHouse Security Cluster.

Importing and Exporting Data in ORC Format

• Import data in ORC format.
  • Non-security cluster

    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"

  • Security cluster

    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: indicates the user-defined path for storing the file.
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 specified during cluster creation.
7. INSERT INTO: indicates the target data table.
8. config-file ./config.xml: indicates the configuration file. For details, see Using a Client to Connect to a ClickHouse Security Cluster.

• Export data in ORC format.
  • Security cluster

    ./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

  • Non-security cluster

    ./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 specified during cluster creation.
6. config-file ./config.xml: indicates the configuration file. For details, see Using a Client to Connect to a ClickHouse Security Cluster.
7. select * from: indicates the target data table.
8. /opt/student.orc: indicates the user-defined path for storing the file.

Importing and Exporting Data in JSON Format

• Import data in JSON format.
  • Non-security cluster

    cat ./jsonnossl.json | ./clickhouse client --host 192.168.x.x --port port --user admin --password password --database test010 --query="INSERT INTO test141 FORMAT JSON"

  • Security cluster

    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 user-defined path for storing the file.
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 specified during cluster creation.
7. INSERT INTO: indicates the target data table.
8. config-file ./config.xml: indicates the configuration file. For details, see Using a Client to Connect to a ClickHouse Security Cluster.