1. 安装

docker-compose.yml
version: '3'
services:
  clickhouse:
    container_name: clickhouse
    image: yandex/clickhouse-server
    ports:
      - 39000:9000
      - 38123:8123
    networks:
      - clickhouse
    volumes:
      - ~/volumes/clickhouse/:/var/lib/clickhouse
      - ./users.xml:/etc/clickhouse-server/users.xml
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

networks:
  clickhouse:
users.xml
<?xml version="1.0"?>
<yandex>
    <profiles>
        <default>
            <max_memory_usage>10000000000</max_memory_usage>
            <use_uncompressed_cache>0</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
        </default>
        <readonly>
            <readonly>1</readonly>
        </readonly>
    </profiles>
    <users>
        <default>
            <password>123123</password>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
        </default>
    </users>
    <quotas>
        <default>
            <interval>
                <duration>3600</duration>
                <queries>0</queries>
                <errors>0</errors>
                <result_rows>0</result_rows>
                <read_rows>0</read_rows>
                <execution_time>0</execution_time>
            </interval>
        </default>
    </quotas>
</yandex>

2. 数据类型

2.1. 数值

  • 整数

    • Int8

    • Int16

    • Int32

    • Int64

    • UInt8

    • UInt16

    • UInt32

    • UInt64

  • 浮点数

    • Float32

    • Float64

  • 定点数

    • Decimal32

    • Decimal64

    • Decimal128

2.2. 字符串

  • String

  • FixedString

  • UUID

2.3. 时间

  • DateTime

  • DateTime64

  • Date

2.4. 特殊类型

  • Array

  • Tuple

  • Enum

  • Nested

  • Nullable

  • IPv4

  • IPv6

3. DDL

3.1. 创建数据库

create database if not exists <db_name> [ENGINE = <engine>]
数据库Engine

  • Ordinary: 默认引擎

  • Dictionary: 字典引擎

  • Memory: 内存引擎, 用于存放临时数据. 数据库重启后数据会被清除.

  • Lazy: 日志引擎.

  • MySQL: MySQL引擎, 自动拉取远端MySQL中的数据, 为它们创建MySQL表引擎的数据表.

3.2. 创建表

3.2.1. 定义表

create table if not exists [<database.]<table_name> (
    <name> [<type>] [DEFAULT|MATERIALIZED|ALIAS <expr>]
) ENGINE = <engine>

3.2.2. 复制表结构

create table if not exists [<database>.]<table_name> AS [<source_database>.]<source_table> [ENGINE = <engine>]

3.2.3. select创建

create table if not exists [<database>.]<table_name> ENGINE = <engine> AS select * from <source_table>