SQL Reference

English 中文 Русский 日本語

Overview Distinctive Features Performance History Adopters

Getting Started

Example Datasets

Introduction GitHub Events Yandex.Metrica Data Recipes Dataset Star Schema Benchmark WikiStat Terabyte Click Logs from Criteo AMPLab Big Data Benchmark Brown University Benchmark New York Taxi Data OpenSky UK Property Price Paid Cell Towers Menus OnTime

Installation Tutorial Playground

Introduction Command-Line Client Native Interface (TCP) HTTP Interface MySQL Interface Input and Output Formats JDBC Driver ODBC Driver C++ Client Library Third-Party

Third-Party Interfaces Client Libraries Integrations Visual Interfaces Proxies

Table Engines for Integrations ODBC JDBC MySQL MongoDB HDFS S3 SQLite Kafka EmbeddedRocksDB RabbitMQ PostgreSQL ExternalDistributed MaterializedPostgreSQL

Introduction MergeTree Family

Introduction MergeTree Data Replication Custom Partitioning Key ReplacingMergeTree SummingMergeTree AggregatingMergeTree CollapsingMergeTree VersionedCollapsingMergeTree GraphiteMergeTree

Introduction StripeLog Log TinyLog

Special Table Engines Distributed Dictionary Merge File Null Set Join URL View MaterializedView Memory Buffer External Data GenerateRandom

Database Engines

Introduction MaterializedMySQL MaterializedPostgreSQL MySQL Lazy Atomic SQLite PostgreSQL Replicated

Overview ALL ARRAY JOIN DISTINCT FORMAT FROM GROUP BY HAVING INTO OUTFILE JOIN LIMIT LIMIT BY OFFSET ORDER BY PREWHERE SAMPLE UNION WHERE WITH

INSERT INTO CREATE

Overview DATABASE TABLE VIEW DICTIONARY FUNCTION USER ROLE ROW POLICY QUOTA SETTINGS PROFILE

ALTER COLUMN PARTITION SETTING DELETE UPDATE ORDER BY SAMPLE BY INDEX CONSTRAINT TTL USER QUOTA ROLE ROW POLICY SETTINGS PROFILE PROJECTION VIEW

SYSTEM SHOW GRANT EXPLAIN REVOKE ATTACH CHECK DESCRIBE DETACH DROP EXISTS KILL OPTIMIZE RENAME EXCHANGE SET SET ROLE TRUNCATE USE WATCH

Syntax Distributed DDL Functions

Introduction Arithmetic Arrays Comparison Logical Type Conversion Dates and Times Strings For Searching in Strings For Replacing in Strings Conditional Files Mathematical Rounding Working with maps Splitting and Merging Strings and Arrays Bit Bitmap Hash Pseudo-Random Numbers Encoding UUID URLs IP Addresses JSON External Dictionaries Yandex.Metrica Dictionaries IN Operator arrayJoin Geo

Geographical Coordinates Geohash H3 Indexes S2 Geometry

Nullable Machine Learning Introspection Tuples Encryption NLP Other

Aggregate Functions

Introduction Reference

count min max sum avg any stddevPop stddevSamp varPop(x) varSamp covarPop covarSamp anyHeavy anyLast argMin argMax avgWeighted corr topK topKWeighted groupArray groupUniqArray groupArrayInsertAt groupArrayMovingSum groupArrayMovingAvg groupArraySample groupBitAnd groupBitOr groupBitXor groupBitmap groupBitmapAnd groupBitmapOr groupBitmapXor sumWithOverflow deltaSum deltaSumTimestamp sumMap minMap maxMap sumCount rankCorr sumKahan intervalLengthSum skewPop skewSamp kurtPop kurtSamp uniq uniqExact uniqCombined uniqCombined64 uniqHLL12 uniqTheta quantile quantiles Functions quantileExact Functions quantileExactWeighted quantileTiming quantileTimingWeighted quantileDeterministic quantileTDigest quantileTDigestWeighted quantileBFloat16 median simpleLinearRegression stochasticLinearRegression stochasticLogisticRegression categoricalInformationValue studentTTest welchTTest entropy mannWhitneyUTest

Combinators Parametric

Table Functions

Introduction file merge numbers remote url mysql postgresql jdbc odbc hdfs s3 input generateRandom cluster view null function dictionary function s3Cluster sqlite

Introduction External Dictionaries

General Description Configuring an External Dictionary Storing Dictionaries in Memory Dictionary Updates Sources of External Dictionaries Dictionary Key and Fields Hierarchical dictionaries Polygon Dictionaries With Grids

Internal Dictionaries

Introduction UInt8, UInt16, UInt32, UInt64, UInt256, Int8, Int16, Int32, Int64, Int128, Int256 Float32, Float64 Decimal Boolean String FixedString(N) UUID Date Date32 DateTime DateTime64 Enum LowCardinality Array(T) AggregateFunction Nested Data Structures

Nested(Name1 Type1, Name2 Type2, ...)

Tuple(T1, T2, ...) Nullable Special Data Types

Expression Set Nothing Interval

Overview IPv4 IPv6

Multiword Type Names Geo Map(key, value) SimpleAggregateFunction

Operators IN Operators

ANSI Compatibility Window Functions

Window Functions

Overview Applying CatBoost Models

Introduction Requirements Monitoring Troubleshooting ClickHouse Upgrade Access Control and Account Management External User Authenticators and Directories

Introduction Kerberos LDAP

Data Backup Configuration Files Quotas Optimizing Performance

Query Profiling

System Tables system.asynchronous_metric_log system.asynchronous_metrics system.clusters system.columns system.contributors system.crash_log system.current_roles system.data_skipping_indices system.data_type_families system.databases system.detached_parts system.dictionaries system.disks system.distributed_ddl_queue system.distribution_queue system.enabled_roles system.errors system.events system.functions system.grants system.graphite_retentions system.licenses system.merge_tree_settings system.merges system.metric_log system.metrics system.mutations system.numbers system.numbers_mt system.one system.opentelemetry_span_log system.part_log system.parts system.parts_columns system.processes system.query_log system.query_thread_log system.query_views_log system.quota_limits system.quota_usage system.quotas system.quotas_usage system.replicas system.replicated_fetches system.replication_queue system.role_grants system.roles system.row_policies system.settings system.settings_profile_elements system.settings_profiles system.stack_trace system.storage_policies system.table_engines system.tables system.text_log system.time_zones system.trace_log system.users system.zookeeper system.zookeeper_log

Server Configuration Parameters

Introduction Server Settings

Testing Hardware Settings

Introduction Permissions for Queries Restrictions on Query Complexity Settings Profiles Constraints on Settings User Settings MergeTree tables settings Settings

Overview clickhouse-copier clickhouse-local clickhouse-benchmark clickhouse-format ClickHouse compressor ClickHouse obfuscator clickhouse-odbc-bridge

OpenTelemetry Support Caches ClickHouse Keeper External Disks for Storing Data

For Beginners Architecture Overview Continuous Integration Checks Build on Linux Build on Mac OS X Build on Linux for Mac OS X Build on Linux for AARCH64 (ARM64) C++ Guide Testing Third-Party Libraries Used Source Code Browser CMake in ClickHouse How to add test queries to ClickHouse CI

Cloud Support Introduction

General Use Cases Operations Integration

2021 2020 2019 2018 2017

Roadmap Security Changelog What’s New in ClickHouse?

SQL Reference

ClickHouse supports the following types of queries:

SELECT
INSERT INTO
CREATE
ALTER
Other types of queries

Rating: 2.9 - 25 votes

Was this content helpful?

★★★☆☆

©2016–2021 ClickHouse, Inc.

Built from b764b6ce21