31:T4f5,## Get Your Own ClickHouse Database For the remaining content of this tutorial, the ClickHouse demo server that we have been using won't cut it anymore. We aren't allowed to make our own tables or insert our own data on that server. To get your own server you can either install a local version of ClickHouse onto your computer or get access to a cloud-hosted ClickHouse instance. - [Installation Instructions](https://clickhouse.com/docs/en/install/#self-managed-install) - ClickHouse only supports any Linux, FreeBSD, or macOS with x86-64, ARM, or PowerPC64LE CPU architecture. It can also run on windows within a virtual machine or WSL. ClickHouse also runs in Docker. - [Sign Up for ClickHouse Cloud Free Trial](https://clickhouse.cloud/signUp) Please input the URL and credentials to your ClickHouse server to proceed. If you installed ClickHouse on your own computer then the default credentials below are probably exactly what you need. Note: - The handling of passwords in this notebook is unsafe so if you have a sensitive password or an important database please consider using a different database client - I have run into issues using this notebook on local ClickHouse servers using Brave Browser. Switching to Firefox fixed the issue for me. 32:T60f,viewof insertIntoValues = editor({ value: `INSERT INTO people (id, name, email, body_height, last_visit) VALUES (1, 'Andreas Triggel', 'atriggel0@rakuten.co.jp', 173, '2020-03-05'), (2, 'Oneida Waylett', 'owaylett1@behance.net', 179, '2018-09-11'), (3, 'Nikkie De Cleen', 'nde2@telegraph.co.uk', 180, '2014-12-28'), (4, 'Tasha O''Roan', 'toroan3@slashdot.org', 174, '2018-04-27'), (5, 'Pru Howley', 'phowley4@w3.org', 174, '2020-10-08'), (6, 'Marina Schmuhl', 'mschmuhl5@businesswire.com', 178, '2021-03-05'), (7, 'Hyatt Duddan', 'hduddan6@oracle.com', 178, '2015-04-26'), (8, 'Abra Penchen', 'apenchen7@slideshare.net', 165, '2023-01-04'), (9, 'Pepito Peacock', 'ppeacock8@merriam-webster.com', 177, '2011-06-08'), (10, 'Judi McGrill', 'jmcgrill9@163.com', 170, '2014-09-15'), (11, 'Danika Wiersma', 'dwiersmaa@nih.gov', 175, '2015-08-16'), (12, 'Ario Frigot', 'afrigotb@census.gov', 168, '2014-12-27'), (13, 'Molly Gregson', 'mgregsonc@ask.com', 176, '2016-01-14'), (14, 'Anatola Lambourn', 'alambournd@bing.com', 166, '2013-05-18'), (15, 'Niles Jobin', 'njobine@time.com', 172, '2017-12-18'), (16, 'Kailey Trotman', 'ktrotmanf@cargocollective.com', 178, '2022-11-07'), (17, 'Cordy Aicheson', 'caichesong@blogtalkradio.com', 186, '2021-09-01'), (18, 'Orton Wintersgill', 'owintersgillh@army.mil', 174, '2016-06-09'), (19, 'Augustus Rivelin', 'arivelini@cocolog-nifty.com', 177, '2017-03-02'), (20, 'Aime Gribbon', 'agribbonj@cmu.edu', 176, '2010-07-09'); `, style: { height: "350px" }, theme: "xcode" })33:T55e, #### `clickhouse-client` `clickhouse-client` is a command-line-based database client. You can use it to run any SQL queries. It can also be used to insert data from the client side. This means `clickhouse-client` will send the file contents to the ClickHouse server process whether that server is located on your computer or in the cloud. The following command in an UNIX terminal with `clickhouse-client` installed will import `iris.csv` ```bash clickhouse-client --query "INSERT INTO iris FORMAT CSV" < iris.csv ``` #### `FROM INFILE` Alternatively the `FROM INFILE ... FORMAT ` clause can be used to specify a file on the client side. ```sql INSERT INTO iris FROM INFILE 'iris.csv' FORMAT CSV; ``` #### The `file()` Table Function We can import the data into a table of our making by treating the `CSV` (or other format) file as if it was a table in the database. Syntax: `file(relative_filepath [,format] [,structure])` This function allows you to access files on the ClickHouse server host machine (as opposed to the client side files accessed in the prior INSERT commands). It's only able to access the directory set in `config.xml` as `user_files_path` for security reasons. The default `user_files_path` is `/var/lib/clickhouse/user_files/`. Let's try importing `iris.csv` again. `iris.csv` has already been saved within the `user_files_path`.34:T473,#### Read More - [ClickHouse Documentation](https://clickhouse.com/docs/en/sql-reference/statements/alter/column) #### Mutations `ALTER` queries are executed as background processes called "mutations". Most importantly, mutations are asynchronous by default, so while your `ALTER` query may have appeared to have finished executing instantly, the background process may not yet be done. Any `SELECT` queries that are executed before the mutation is complete may see data from parts that have been mutated as well as parts that have not yet been mutated. Progress of mutations can be tracked using the `system.mutations` table, one of the system tables we will cover later. #### Read More - [ClickHouse Documentation](https://clickhouse.com/docs/en/sql-reference/statements/alter/#mutations) ### `RENAME` and `EXCHANGE` Briefly, these statements handle renaming and swapping the names of tables respectively. #### Read More - [ClickHouse `RENAME` Documentation](https://clickhouse.com/docs/en/sql-reference/statements/rename) - [ClickHouse `EXCHANGE` Documentation](https://clickhouse.com/docs/en/sql-reference/statements/exchange)35:T512,## Other useful data types within ClickHouse ClickHouse has many more data types than the widely supported in other SQL Databases. See the full list in [the documentation](https://clickhouse.com/docs/en/sql-reference/data-types/). I'll cover two that I've found very useful to have knowledge of. These may or may not be supported in other DBMS. ### `LowCardinality(data_type)` For columns that store "low cardinality" values or columns with fewer distinct values, the `LowCardinality` data type can improve performance by applying a [dictionary coding](https://en.wikipedia.org/wiki/Dictionary_coder). The documentation recommends using this type for columns with less than 10,000 distinct values. With more than 100,000 unique values, this can worsen performance. `LowCardinality(data_type)` can be applied to another data type. For example `LowCardinality(String)`. If we created the `people` table using `LowCardinality(UInt8)` type for the `age` column, it would look like the following. ```sql CREATE TABLE default.people ( `id` Int64, `name` String, `email` String, `age` LowCardinality(UInt8), `body_height` Int16, `last_visit` Date ) ENGINE = MergeTree ORDER BY id; ``` We can try changing the `age` column of the `people` table to a `LowCardinality(UInt8)`.36:T62b,
### `Nullable(data_type)` and `NULL` `NULL` itself is the idea of nothingness in many programming languages including ClickHouse. In the context of a database, sometimes we don't know the information that belongs in a cell on the table. If we don't know a person's age, then that value shouldn't be zero. It might make more sense to leave it blank. In the same vein, if someone neglected to give us their email address or we forget to write down, then it's somewhat inaccurate to denote it as an empty string (`''`) which might mean that the email address itself contains no characters. Again it might make sense to leave it completely blank, but that looks exactly the same as an empty string (`''`) when printed out. So we've invented a new term that denotes a blank value called `NULL`. This is routinely used to denote "there is nothing here" or "we don't know yet". Incidentally, some other languages try to make a further distinction between "nothing here" and "we don't know yet". In Clickhouse *nullable* columns are allowed to contain a `NULL` value while other columns must contain non-null values. We can declare that a column is nullable using either the `NULL` modifier or the `Nullable(data_type)` data type. If we created the `people` table with a nullable `email` column, it would look like this. ```sql CREATE TABLE default.people ( `id` Int64, `name` String, `email` Nullable(String), `age` UInt8, `body_height` Int16, `last_visit` Date ) ENGINE = MergeTree ORDER BY id ``` Let's change the email field to allow `NULL` values.37:T5a2,`email` is `NULL`, hooray! But also, notice that `body_height` is 0. We didn't actually give a value for `body_height` in our `INSERT` query. We forgot to give it a value, or we didn't know the height, but surely Steve doesn't have a body height of zero centimeters. #### Missing, Non-Null Values `body_height` is not a Nullable column so the values in that column must be integers (specifically `Int16`). Without a value, the column defaults to a default value. For numbers the default value is usually `0`. For dates, it's midnight of `'1970-01-01'` in the GMT time zone. For Strings, it's the empty string (`''`). When creating or modifying tables, a different default value can be specified using the `DEFAULT` modifier. Not only do these default values come into play during `INSERT` queries, this will also happen in results of `JOIN`s when there is missing information. It's important to look out for this possibility so you don't add unexpected values to your tables. Here is an example of joining one table with numbers 1-5 to another table with numbers 2-6. The number 6 is missing from the `1-5` table. And the number 1 is missing from the `2-6` table. Thus these values default to zero. Unfortunately this happens even if we are adding the results of the join to a table with nullable columns. We can instruct ClickHouse to use `NULL` to denote missing values by adding a `SETTINGS join_use_nulls=1` at the end of the query.38:T8f3,#### Read More - [ClickHouse Nullable Data Type Documentation](https://clickhouse.com/docs/en/sql-reference/data-types/nullable/#data_type-nullable) - [ClickHouse Documentation About NULL or NOT NULL Modifiers](https://clickhouse.com/docs/en/sql-reference/statements/create/table#null-or-not-null-modifiers) - [ClickHouse Documentation About Processing of Empty or Null Cells in `JOIN`s](https://clickhouse.com/docs/en/sql-reference/statements/select/join/#usage-recommendations) - [ClickHouse Documentation About the `DEFAULT` Type Modifier](https://clickhouse.com/docs/en/sql-reference/statements/create/table#default) ### Table Engines Table engines in ClickHouse determine how and where data is stored among other implementation details. Some of ClickHouse's features are available only in certain table engines. There are two major families of table engines in ClickHouse: MergeTree and Log. Many more table engines exist including those for integrating with other tools and many that implement specific features. A comprehensive list can be found in the [documentation](https://clickhouse.com/docs/en/engines/table-engines/). #### MergeTree These are the bread and butter tables, specialized for high-load tasks and have exclusive support for many ClickHouse features. Specialized versions of MergeTree allow additional optimizations by modifying how MergeTree works. - `ReplacingMergeTree` - Removes rows with duplicate sort keys - `SummingMergeTree` - Helps compute summarized values on numeric columns for rows with the identical sorting key. - `AggregatingMergeTree` - Helps compute aggregation functions on specified columns for rows with identical sorting key. Stores a combination of states of aggregate functions. - `CollapsingMergeTree` - Useful for saving continuously changing data without the inefficient `UPDATE` queries. #### Log The Log table engine is for writing many small tables (up to ~1 million rows) without using other features. - `Log` - Parallel data reading. One file for each column. - `StripeLog` - Parallel data reading. One file per table. #### Read More - [ClickHouse Table Engines Documentation](https://clickhouse.com/docs/en/engines/table-engines/) - [Secrets of ClickHouse Query Performance Talk](https://www.youtube.com/watch?v=6WICfakG84c)39:T64b,### Materialized View Materialized views store the result of your `SELECT` query. As the tables involved in the query change, the materialized view is updated to ensure the result of your query stays accurate. This technique speeds up time-consuming queries by storing and pre-computing the results. Note that different DBMS have different ways of implementing Materialized Views, so they may work slightly differently in other databases. Importantly, materialized views do not run the `SELECT` query at the time of their creation by default. So their default behavior is only performing the query on new rows that are added to the table. Best practice is to use `INSERT` to add the data to the destination table to initially populate the materialized view. Note that the column names in materialized views respect the column names in the result set of your `SELECT` query. It does not respect the order of the columns in the result set. So be sure to name things appropriately using `AS`. The implementation and behavior of Materialized Views in ClickHouse is quite complicated (and still confuses me), so please read more to understand their nuances before using them. #### Read More - [Optimizing ClickHouse: Materialized View](https://levelup.gitconnected.com/optimizing-clickhouse-materialized-view-e4ecbdffa08e) - [Everything Things Should Know Know About Materialized Views (In ClickHouse)](https://den-crane.github.io/Everything_you_should_know_about_materialized_views_commented.pdf) - [Cascading Materialized Views](https://clickhouse.com/docs/en/guides/developer/cascading-materialized-views/)3a:T73c,## Integration There are a vast variety of ways of integrating ClickHouse into other tools that you may be familiar with. #### Language Clients At the time of writing, the ClickHouse team maintains client libraries in Python, NodeJS, Java, and GO for connecting to ClickHouse database servers. You can check the [documentation here](https://clickhouse.com/docs/en/integrations/language-clients) to learn to use them. If you are not using one of these languages, there are [libraries maintained by third-parties](https://clickhouse.com/docs/en/interfaces/third-party/client-libraries) that cover a slew of other programming languages. #### SQL Clients A handful of database management tools are able to act as SQL Clients for ClickHouse. These database management tools aka database integrated development environments (IDEs) help store your queries, display your result, generate graphs using your results, and more. Notably this Observable Notebook you are using acts as a SQL Client and so can a Jupyter Notebook. - [The official list is here.](https://clickhouse.com/docs/en/integrations/sql-clients) - [Here are additional third-party visual interfaces.](https://clickhouse.com/docs/en/interfaces/third-party/gui) #### HTTP Interface There are a [few network interfaces](https://clickhouse.com/docs/en/interfaces/overview) through which information can go in and out of the database. One of these is the [`HTTP` interface](https://clickhouse.com/docs/en/interfaces/http). This provides you a SQL Client accessible via your favorite web browser (like Google Chrome) not to mention any process that can access the internet. You can find a user interface by adding `/play` to the end of the address of your database host. For example: [`${chHost}/play`](${chHost.match(/https?:\/\//) ? chHost : 'http://' + chHost}/play?user=${chUsername})3b:Td93,## Optimization There are many strategies for structuring your tables and queries for very efficient processing of your queries. ClickHouse further provides us with many tools for improving performance based on your particular use case, typically making some trade-offs. Often, the goal is to reduce the number of times that ClickHouse must read from or write to the disk, a very slow task. Optimizations often require a low level understanding of how the database works, so many of these techniques are out of scope of this tutorial. A good starting point to learn to optimization for ClickHouse is [the guide provided by the ClickHouse Documentation](https://clickhouse.com/docs/en/optimize). Nevertheless, we can briefly introduce some basics. #### Rules of Thumb - [Avoid Mutations](https://clickhouse.com/docs/en/optimize/avoid-mutations) - aka `ALTER` queries - [Avoid Nullable Columns](https://clickhouse.com/docs/en/optimize/avoid-nullable-columns) - [Insert Data In Bulk](https://clickhouse.com/docs/en/optimize/bulk-inserts) - [Choose a Low Cardinality Partition Key](https://clickhouse.com/docs/en/optimize/partitioning-key) #### Read (or Watch) More - [A Day in the Life of a ClickHouse Query](https://www.youtube.com/watch?v=XpkFEj1rVXg) - [Secrets of ClickHouse Query Performance](https://www.youtube.com/watch?v=6WICfakG84c) ### Indexes Just like the "index" in the back of a textbook, indexes in databases help you find what you're looking for much faster than scanning word by word. However, creating them involves some foresight about how you will query your data. "The Magic of SQL" YouTube channel explains the trade-offs very well. (Note that he uses SQL Server instead of ClickHouse) - [Introduction to Indexing](https://youtube.com/playlist?list=PL78V83xV2fYlLA-bjMU2ZvUKQOZNrqLEa) - [How to Create Database Indexes](https://www.youtube.com/watch?v=7wLFr7ZnKPU) Indexes in ClickHouse behave differently from that of traditional RDBMS. The ClickHouse documentation again provides very good materials for understanding the details about building indexes in ClickHouse - [Primary Indexes](https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-intro) - [Data Skipping Indexes](https://clickhouse.com/docs/en/guides/improving-query-performance/skipping-indexes#skip-best-practices) #### Rules of Thumb - Choose your sorting key (specified in the `ORDER BY` clause of `CREATE TABLE`) wisely. It should reflect what you will frequently filter by in a `WHERE` clause and/or aggregate by with `GROUP BY`. If you often filter by multiple columns, then you can create a composite primary index combining all of those columns. - For a composite primary index (composed of multiple columns), sort the columns of the index in order of increasing cardinality. - Adding too many columns into your indices may slow down inserts. Also having a large index that is too large to fit into memory may be detrimental to performance. - A data skipping index is useful for columns that you filter by that vary dependent on your primary index columns. - In ClickHouse, we aren't able to make a second index on a column completely unrelated to your primary index as is possible in other DBMS. If you must optimize multiple queries that filter by very different columns, then it's recommended to make a materialized view or a second table that contains the same information but uses a different primary index. 3c:T569,## Configuration There are countless things you can configure on a ClickHouse server. ### Settings Settings are able to change many things from toggling flags unlocking experimental features to preferring certain algorithms for efficiency to limiting memory use. The `system.settings` table contains current values of settings. Settings can be modified within `users.xml`, within a session using the `SET setting_name=value [, setting_2 = value];` and within the SQL client. #### Read More - [ClickHouse Documentation on Settings](https://clickhouse.com/docs/en/operations/settings/) ### Configuration Files Configuration files for ClickHouse are useful for setup and database administration tasks such as deciding where the data should be stored, which folder the `file()` table function can access, configuration of the HTTP interface, as well as users and their default settings and passwords. The configuration is stored at `/etc/clickhouse-server/config.xml` and `/etc/clickhouse-server/users.xml` in either YAML or XML format. Modified versions of these files should be kept in `/etc/clickhouse-server/config.d/config.xml` and `/etc/clickhouse-server/users.d/users.xml` so that they will not get overwritten by ClickHouse software updates. #### Read More - [ClickHouse Documentation on configuration files](https://clickhouse.com/docs/en/operations/configuration-files)3d:[{"id":"5acf54989c2155c1ad0c6dcb7433fc703e3464572d573c9873dc6d38863339d5e3178453f2ce927068ebe471a97c8e28cfdb1f927513263259c88bdcbfb653ec","href":"https://static.observableusercontent.com/files/5acf54989c2155c1ad0c6dcb7433fc703e3464572d573c9873dc6d38863339d5e3178453f2ce927068ebe471a97c8e28cfdb1f927513263259c88bdcbfb653ec","name":"clickhouse-mark-4238843745.png","type":"image/png","lastModified":1677501904838.544,"size":7715},{"id":"c305157b5bcc3398280273ac9bd87ec2c644ae978b7ab846478d8fee6ee5bbfd1f6813bf2fe58b0dfe0b919701c8ba34dce4726b264781aeae318d5928709617","href":"https://static.observableusercontent.com/files/c305157b5bcc3398280273ac9bd87ec2c644ae978b7ab846478d8fee6ee5bbfd1f6813bf2fe58b0dfe0b919701c8ba34dce4726b264781aeae318d5928709617","name":"iris.csv","type":"text/csv","lastModified":1677502391444.513,"size":4551}] 2f:{"title":"ClickHouse SQL Workshop Day 4","cells":[{"id":0,"value":"# ClickHouse SQL Workshop Day 4\n\n${await FileAttachment(\"clickhouse-mark-4238843745.png\").image()}\n\nThis tutorial aims to introduce beginners to the topic of relational and non-relational databases and SQL in the context of data analytics using [ClickHouse](https://clickhouse.com/), a fast open-source OLAP DBMS.\n\nThis tutorial uses an [Observable Notebook](https://observablehq.com/@observablehq/observable-overview-video?collection=@observablehq/getting-started) to give you an interactive experience. Note that ClickHouse is usually used via other clients such as `clickhouse-client`, [DataGrip](https://www.jetbrains.com/datagrip/), and [DBeaver](https://dbeaver.io/). If you accidentally change the cells of this notebook, you can undo the changes by pressing the \"Discard\" button on the top bar or by refreshing the page.\n","pinned":false,"hidden":false,"mode":"md"},{"id":32,"value":"toc({\n headers: \"h2,h3\",\n skip: [\n \"Imports\",\n \"Topics\",\n \"SQL Cells\",\n \"Exercises\",\n \"Other great references for ClickHouse\",\n \"Appendix\",\n \"What we will be covering\",\n \"What won't be covered\"\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2989,"value":"## Before We Get Started\n\n- Give everyone the URL to my local server.\n- Create a separate database for this demonstration.\n- If you want to run queries in this notebook during the presentation, please take care to change the table names so that the tables you create don't interfere with tables that others create.","pinned":false,"hidden":false,"mode":"md"},{"id":2233,"value":"$31","pinned":false,"hidden":false,"mode":"md"},{"id":2236,"value":"viewof chHost = Inputs.text({\n label: \"Database Host URL\",\n value: \"localhost:8123\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2242,"value":"viewof chUsername = Inputs.text({ label: \"Username\", value: \"default\" })","pinned":false,"hidden":false,"mode":"ojs"},{"id":2245,"value":"viewof chPassword = Inputs.password({ label: \"Password\" })","pinned":false,"hidden":false,"mode":"ojs"},{"id":2248,"value":"viewof chDatabaseName = Inputs.text({\n label: \"Database Name\",\n value: \"default\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":74,"value":"## Creating Tables\n\nWe can create tables using `CREATE TABLE ` followed by a list of columns, their data types, and constraints. ","pinned":false,"hidden":false,"mode":"md"},{"id":2195,"value":"viewof createTable = editor({\n language: \"sql\",\n value: `CREATE TABLE people (\n\tid Int64,\n\tname String,\n\temail String,\n\tbody_height Int16,\n\tlast_visit Date\n)\nENGINE MergeTree\nORDER BY id;`,\n style: { height: \"150px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2270,"value":"mutationQueryButton(createTable)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2380,"value":"The `ENGINE` clause defines the \"Table Engine\". This is unique to ClickHouse, not used in other databases. When in doubt, use the `MergeTree` table engine. We will discuss the types of Table Engines further in a later section. \n\nThe `ORDER BY` clause specifies the sorting key. This is necessary for the `MergeTree` table engine. Usually, this should be the same as the Primary Key which will be used to index the table. We will cover this more in the Optimization section. If there are two or more columns within the sorting key or the primary key, surround them with parentheses (like so: `(key1, key2)`) to make them a `tuple`. If you do not care about the order the rows are saved in, use `ORDER BY tuple()`. \n\n#### Remove Table\n\nDelete tables using the `DROP TABLE

` command.","pinned":false,"hidden":false,"mode":"md"},{"id":2353,"value":"viewof dropTable = editor({\n value: \"DROP TABLE people;\",\n style: { height: \"50px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2361,"value":"mutationQueryButton(dropTable)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2449,"value":"#### Copy Schema of Another Table\n\nIf you need to create a table with identical columns and datatypes you can use `CREATE TABLE ... AS ` to copy the *schema* of the table. \n\n","pinned":false,"hidden":false,"mode":"md"},{"id":2439,"value":"viewof createTableAsSchema = editor({\n value: `CREATE TABLE other_people\nAS people\nENGINE MergeTree\nORDER BY name;`,\n style: { height: \"75px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2445,"value":"mutationQueryButton(createTableAsSchema)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2454,"value":"DESCRIBE TABLE other_people","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:ownClient"},{"id":2451,"value":"#### Read More\n\n- [ClickHouse Documentation](https://clickhouse.com/docs/en/sql-reference/statements/create/table)\n- [PostgreSQL tutorial](https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-create-table/)\n- [MergeTree Documentation](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree)","pinned":false,"hidden":false,"mode":"md"},{"id":79,"value":"---\n### Adding Data To Your Table\n\nThere are a few ways to add data to a table, with slight differences within different implementations of SQL.\n\nThis section will use syntax for ClickHouse. This can be done in other popular SQL databases possibly with slightly different syntax. \n\nYou can rerun the SQL cell below to see the changes as we insert more data.","pinned":false,"hidden":false,"mode":"md"},{"id":2376,"value":"SELECT * FROM people ORDER BY id LIMIT 5;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:ownClient"},{"id":2403,"value":"#### `INSERT INTO ... VALUES`\n\n`INSERT INTO` allows you to specify the target table and an optional list of target columns the information will be added into. ","pinned":false,"hidden":false,"mode":"md"},{"id":2337,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":2345,"value":"mutationQueryButton(insertIntoValues)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2370,"value":"#### `INSERT INTO ... SELECT`\n\n`INSERT INTO` also allows you to insert the results of a query into a table.","pinned":false,"hidden":false,"mode":"md"},{"id":2390,"value":"viewof insertIntoSelect = editor({\n value: `INSERT INTO people (id, name, email)\nSELECT rand() % 255, name, email FROM people \nORDER BY 1 LIMIT 5;\n`,\n style: { height: \"50px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2398,"value":"mutationQueryButton(insertIntoSelect)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2408,"value":"#### `CREATE TABLE ... AS`\n\nSimilarly you can create a table using the results of a query.","pinned":false,"hidden":false,"mode":"md"},{"id":2417,"value":"viewof createTableAs = editor({\n value: `CREATE TABLE people_heights (\n id Int64,\n height Int16\n) ENGINE MergeTree\nORDER BY id AS\nSELECT \n id,\n body_height\nFROM people;`,\n style: { height: \"150px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2428,"value":"mutationQueryButton(createTableAs)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2431,"value":"SELECT * FROM people_heights LIMIT 5","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:ownClient"},{"id":2472,"value":"---\n### Importing Data From a File\n\nUsing a combination of `CREATE TABLE` and `INSERT` we can import our own data into ClickHouse. \n\nThere are a multitude of ways to import your data into ClickHouse from the command-line to connections with other DBMS to Amazon s3 storage buckets. \n\nIn this section we'll demonstrate importing data from a file using two of these strategies. \n- `clickhouse-client` in the terminal\n- `FROM INFILE` clause\n- `file()` table function\n\nLet's import the iris dataset! - [Original Source](https://archive.ics.uci.edu/ml/datasets/Iris/), [Download](${irisDownloadUrl})\n\nNote that this particular `iris.csv` happens to not contain any header row. The first row it has is data. There are no column names. Thus I am using `CSV` format. If our `CSV` had headers, we would use `CSVWithNames`.\n[See more supported file formats here.](https://clickhouse.com/docs/en/interfaces/formats/)\n\nLet's create an empty table to insert our data into.","pinned":false,"hidden":false,"mode":"md"},{"id":2474,"value":"viewof createTableIris = editor({\n value: `CREATE TABLE iris (\n sepal_length Float32,\n sepal_width Float32,\n petal_length Float32,\n petal_width Float32,\n species String\n) \nENGINE MergeTree\nORDER BY tuple();`,\n style: { height: \"150px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2478,"value":"mutationQueryButton(createTableIris)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2374,"value":"$33","pinned":false,"hidden":false,"mode":"md"},{"id":2488,"value":"viewof insertIntoIrisUsingFile = editor({\n value: `INSERT INTO iris \nSELECT \n sepal_width,\n sepal_length,\n petal_width,\n petal_length,\n species\nFROM file('iris.csv', CSV, '\n sepal_width Float32,\n sepal_length Float32,\n petal_width Float32,\n petal_length Float32,\n species String\n');`,\n style: { height: \"225px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2494,"value":"mutationQueryButton(insertIntoIrisUsingFile)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2497,"value":"Notice the structure definition as the third parameter of the table function. If the structure is not provided, ClickHouse will try to infer it. For example, `INSERT INTO iris SELECT * FROM file('iris.csv', CSV)` will work fine. However, defining the structure gives you more flexibility with expression list in the `SELECT` statement.","pinned":false,"hidden":false,"mode":"md"},{"id":2484,"value":"\n#### Read More\n- [ClickHouse Documentation - Working with CSV and TSV data](https://clickhouse.com/docs/en/integrations/data-formats/csv-tsv)\n- [ClickHouse Documentation - `FROM INFILE`](https://clickhouse.com/docs/en/sql-reference/statements/insert-into/#inserting-data-from-a-file)\n- [ClickHouse `file()` Table Function Documentation](https://clickhouse.com/docs/en/sql-reference/table-functions/file/)","pinned":false,"hidden":false,"mode":"md"},{"id":2663,"value":"### `SHOW CREATE TABLE`\n\nA very quick way of checking the schema of a table (or how a table was set up) is the `SHOW CREATE TABLE` query. This constructs a query that would create the table as it currently is. \n\n#### Read More\n- [ClickHouse `SHOW CREATE TABLE` Documentation](https://clickhouse.com/docs/en/sql-reference/statements/show/#show-create-table)","pinned":false,"hidden":false,"mode":"md"},{"id":2414,"value":"---\n## Modifying Tables & `ALTER TABLE`\n\nSometimes we may need to make changes to our tables without destroying and recreating the table. This is achieved with `ALTER TABLE` queries. (These queries are supported only for tables of the MergeTree family.)\n\nThere are many types of `ALTER TABLE` queries, so we'll cover just a couple particularly useful ones. They are exhaustively described in the ClickHouse documentation. \n\n#### Read More\n- [ClickHouse `ALTER` Documentation](https://clickhouse.com/docs/en/sql-reference/statements/alter/)\n\n#### `ALTER TABLE ... UPDATE`\n\nSyntax: `ALTER TABLE table_name UPDATE column1 = expr1 [, ...] WHERE filter_expr`\n\nChange values of columns for rows matching a filter. \n","pinned":false,"hidden":false,"mode":"md"},{"id":2505,"value":"viewof alterTableUpdate = editor({\n value: `ALTER TABLE people\nUPDATE body_height = 180,\n email = 'foobar@email.com'\nWHERE id = 1;`,\n theme: \"xcode\",\n style: { height: \"75px\" }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2509,"value":"mutationQueryButton(alterTableUpdate)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2514,"value":"SELECT * FROM people WHERE id = 1","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:ownClient"},{"id":2503,"value":"
\n#### Read More\n- https://clickhouse.com/docs/en/sql-reference/statements/alter/update/\n\n#### `ALTER TABLE ... DELETE`\n\nSyntax: `ALTER TABLE table_name DELETE WHERE filter_expr`\n\nRemoves rows that match a filter.","pinned":false,"hidden":false,"mode":"md"},{"id":2522,"value":"viewof alterTableDelete = editor({\n value: `ALTER TABLE people\nDELETE WHERE id = 1\n`,\n style: { height: \"50px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2525,"value":"mutationQueryButton(alterTableDelete)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2528,"value":"SELECT * FROM people WHERE id BETWEEN 0 AND 5","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:ownClient"},{"id":2534,"value":"
\n\n#### Read More\n- https://clickhouse.com/docs/en/sql-reference/statements/alter/delete/\n\n#### `COLUMN`\n\nThere are a series of column manipulations we can perform on existing tables including adding, removing, renaming, and changing the type of columns. \n\nHere we can demonstrate adding a new column to the `people` table.","pinned":false,"hidden":false,"mode":"md"},{"id":2538,"value":"viewof alterTableAddColumn = editor({\n value: `ALTER TABLE people\nADD COLUMN age UInt8\nAFTER email;\n`,\n style: { height: \"75px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2541,"value":"mutationQueryButton(alterTableAddColumn)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2545,"value":"Next we must add information into this table. `rand() % 90` creates a random number from 0 up to and not including 90.","pinned":false,"hidden":false,"mode":"md"},{"id":2547,"value":"viewof addAgeValues = editor({\n value: `ALTER TABLE people\nUPDATE age = rand() % 90\nWHERE true;`,\n style: { height: \"50px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2552,"value":"mutationQueryButton(addAgeValues)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2559,"value":"SELECT * FROM people LIMIT 5;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:ownClient"},{"id":2535,"value":"$34","pinned":false,"hidden":false,"mode":"md"},{"id":2565,"value":"$35","pinned":false,"hidden":false,"mode":"md"},{"id":2571,"value":"viewof alterTableType = editor({\n value: `ALTER TABLE people\nALTER COLUMN age \nTYPE LowCardinality(UInt8);`,\n style: { height: \"50px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2576,"value":"mutationQueryButton(alterTableType)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2581,"value":"DESCRIBE TABLE people","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:ownClient"},{"id":2567,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/data-types/lowcardinality","pinned":false,"hidden":false,"mode":"md"},{"id":2586,"value":"$36","pinned":false,"hidden":false,"mode":"md"},{"id":2605,"value":"viewof alterTableModifyColumn = editor({\n value: `ALTER TABLE people\nMODIFY COLUMN email Nullable(String);`,\n style: { height: \"50px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2608,"value":"mutationQueryButton(alterTableModifyColumn)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2611,"value":"Now let's add a person with a `NULL` email.","pinned":false,"hidden":false,"mode":"md"},{"id":2613,"value":"viewof insertNullEmail = editor({\n value: `INSERT INTO people(id, name, age, last_visit)\nVALUES (999, 'Steve Sterling', 25, '2023-02-27')`,\n style: { height: \"50px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2616,"value":"mutationQueryButton(insertNullEmail)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2624,"value":"Now let's take a look at the person we just added","pinned":false,"hidden":false,"mode":"md"},{"id":2619,"value":"SELECT * FROM people WHERE id = 999","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:ownClient"},{"id":2626,"value":"$37","pinned":false,"hidden":false,"mode":"md"},{"id":2637,"value":"SELECT \n a.number AS `1-5`,\n b.number AS `2-6`\nFROM (SELECT * FROM numbers(1, 5)) AS a -- numbers 1 thru 5\nFULL OUTER JOIN (SELECT * FROM numbers(2, 5)) AS b -- numbers 2 thru 6\nUSING number","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:ownClient"},{"id":2603,"value":"$38","pinned":false,"hidden":false,"mode":"md"},{"id":2723,"value":"## Views\n\nGenerally, views are virtual tables created based on the result of a query. ClickHouse supports many types of views some of which are \"experimental\" at the time of writing.","pinned":false,"hidden":false,"mode":"md"},{"id":2718,"value":"### Normal View\n\nA normal view stores a `SELECT` query. It does not store the result of the query. \nEach time you query information from a normal view, it will run the stored query to retrieve the information. \n\nSyntax: `CREATE VIEW view_name AS SELECT ...`\n\nYou can later query it using `SELECT * FROM view_name`. \n\nThis is effectively the same as `SELECT * FROM (SELECT ...)`\n\n#### Parameterized View\n\nThis is a normal view with some *fill-in-the-blanks*. \n\nSyntax: `CREATE VIEW view_name AS SELECT ...`\n\nHere's an example.","pinned":false,"hidden":false,"mode":"md"},{"id":2734,"value":"viewof createParameterizedView = editor({\n value: `CREATE VIEW people_vw AS\nSELECT * FROM people\nWHERE age >= {min_age: UInt8};`,\n theme: \"xcode\",\n style: { height: \"50px\" }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2737,"value":"mutationQueryButton(createParameterizedView)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2733,"value":"SELECT id, name, age FROM people_vw(min_age=50)","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:ownClient"},{"id":2731,"value":"#### Read More\n- [W3Schools SQL Tutorial](https://www.w3schools.com/SQL/sql_view.asp)\n- [ClickHouse `VIEW` Documentation](https://clickhouse.com/docs/en/sql-reference/statements/create/view/#normal-view)","pinned":false,"hidden":false,"mode":"md"},{"id":81,"value":"$39","pinned":false,"hidden":false,"mode":"md"},{"id":2744,"value":"\n### `DROP VIEW`\n\nTo delete a view, use `DROP VIEW view_name`.\n","pinned":false,"hidden":false,"mode":"md"},{"id":2749,"value":"viewof dropView = editor({\n value: `DROP VIEW people_vw`,\n style: { height: \"50px\" },\n theme: \"xcode\"\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2752,"value":"mutationQueryButton(dropView)","pinned":false,"hidden":false,"mode":"ojs"},{"id":76,"value":"$3a","pinned":false,"hidden":false,"mode":"md"},{"id":83,"value":"$3b","pinned":false,"hidden":false,"mode":"md"},{"id":85,"value":"## System Tables\n\nClickHouse has the most intuitive interface for system internals that I've found in a database thus far. Use system tables including, for example, `system.query_log`, `system.columns`, `system.parts`, `system.mutations`, `system.processes` as if they were any other table.\n\n#### Read More\n- [ClickHouse System Table Documentation](https://clickhouse.com/docs/en/operations/system-tables/)","pinned":false,"hidden":false,"mode":"md"},{"id":2796,"value":"$3c","pinned":false,"hidden":false,"mode":"md"},{"id":94,"value":"## Appendix","pinned":false,"hidden":false,"mode":"md"},{"id":5,"value":"import { client, ClickHouseDatabaseClient } from '@stas-sl/clickhouse-playground'","pinned":false,"hidden":false,"mode":"ojs"},{"id":17,"value":"import { vl } from \"@vega/vega-lite-api\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":28,"value":"import {toc} from \"@nebrius/indented-toc\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":87,"value":"import { test, expect, editor } from \"@samchencode/utilities\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":697,"value":"class UnattemptedExerciseError extends Error {\n name = \"Exercise Not Attempted\";\n constructor() {\n super(\"Modify the SQL Cell below to get started!\");\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":700,"value":"function checkExerciseAttempted(rows) {\n const firstVal = _.values(rows[0])[0];\n if (firstVal === \"Hello World\") {\n throw new UnattemptedExerciseError();\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2205,"value":"function button(promiseOnClick) {\n const el = DOM.element(\"button\");\n el.innerHTML = \"Execute\";\n\n const toHtml = (el, text = \"\", error) =>\n html`${el}

${text}

`;\n\n return Generators.observe((notify) => {\n const handleClick = () => {\n promiseOnClick()\n .then((msg) => notify(toHtml(el, msg)))\n .catch((e) => notify(toHtml(el, e.message, true)));\n };\n notify(toHtml(el));\n el.addEventListener(\"click\", handleClick);\n return () => el.removeEventListener(\"click\", handleClick);\n });\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2331,"value":"mutationQueryButton = (query) =>\n button(() =>\n ownClient.query(query).then((res) => (res.length === 0 ? \"Ok\" : res))\n )","pinned":false,"hidden":false,"mode":"ojs"},{"id":2261,"value":"ownClient = new MyClickHouseClient(\n `https://${chHost.replace(\n /^https?:\\/\\//,\n \"\"\n )}?add_http_cors_header=1&database=${chDatabaseName}&user=${chUsername}&password=${chPassword}&default_format=JSON&allow_suspicious_low_cardinality_types=1`\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2322,"value":"import { ClickHouseDatabaseClient as MyClickHouseClient } from \"c198f0caf41fc2f5\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":2980,"value":"irisDownloadUrl = (await FileAttachment(\"iris.csv\").url()) +\n \"?response-content-disposition=attachment%3Bfilename*%3DUTF-8%27%27iris.csv\"","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W3d","stdlib":"1","titling":"auto"}