2f:T538,# ClickHouse SQL Workshop Day 1 - Presenter Edition *Monday, Feb 6th* ${await FileAttachment("clickhouse-mark-4238843745.png").image()} This 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. This 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. ## SQL Cells Observable Notebook's SQL Client allows you to write and execute your own queries from your own web browser. Feel free to practice your SQL skills as you progress through this tutorial. If you would like to save your changes, feel free to create an ObservableHQ account and fork this notebook. > **Note**: Run cells by pressing SHIFT + ENTER or the blue triangle on the top right of the textbox Cells that allow you to run SQL look like the following:30:T1ca9,## What is a database > Databases are an organized collection of related information This section will attempt to convey a big picture understanding of what databases are and options for working with data before we dive into SQL code. Feel free to skip if you'd like. ### Databases on Computers: Database Management Systems (DBMS) | Your Shopping List | Amazon.com | | --- | --- | | List of products you need | Collects products, reviews, orders. deliveries, users, and more | | Security is usually unimportant | Security is crucial | | Up to 20 items | Trillions of data points | | Would be slightly annoying if it was lost | Downtime is catastrophic to Amazon Services | | Stored in your memory, on paper, or in a notes app | Stored on a computer | DBMS are - Typically modeled in rows and columns in a series of tables. - Data can be easily accessed, managed, modified, updated, controlled, and organized. - Often uses structured query language (SQL) for writing and querying data DBMS helps with - Efficient accessing, management, modification, insertion of data - Security - Backups - Concurrency - Inter-op with other software After this point, I'll use the word "database" to refer specifically to DBMS tools. #### Read (or Watch) More - https://www.youtube.com/watch?v=HXV3zeQKqGY ### Use Cases for DBMS: to SQL or to Excel? ${await FileAttachment("截屏2023-01-21 上午8.39.38.png").image()} Spreadsheets were originally designed for one user, and their characteristics reflect that. They’re great for a single user or small number of users who don’t need to do a lot of incredibly complicated data manipulation. DBMS, on the other hand, are designed to hold much larger collections of organized information. DBMS allow multiple users at the same time to quickly and securely access and query the data using highly complex logic and language. #### Problems That Databases Address | Concern | Solutions other than databases | | --- | --- | | Data Storage | File System & CSVs/Excel, Google Drive, HDFS | | Sharing & Permissions | Email, Google Drive, OneDrive, FTP, P2P file sharing | | [Data Wrangling](https://careerfoundry.com/en/blog/data-analytics/data-wrangling/) | Python: `pandas`, R: `base-R`, `dplyr`, `data.table` | | [Data is very big](https://towardsdatascience.com/what-to-do-when-your-data-is-too-big-for-your-memory-65c84c600585) | Buy more storage or memory, Chunking, Python: `polars`, `vaex`, R: `disk.frame`, Apache Hadoop & Spark | A database is a potential solution to all of these concerns, although some databases are better or worse at solving each problem. When you need to solve many or all of these problems or are facing limitations of other solutions, it may be a good idea to use a database. #### Read More - https://www.oracle.com/database/what-is-database/ - https://h2oai.github.io/db-benchmark/ ### Types of Databases There are a couple ways of classifying databases - OLTP vs OLAP - Relational vs Non-Relational (NoSQL) - Within non-relational, document stores, key value stores, column oriented, and graph - column-oriented vs row-oriented #### OLTP vs OLAP Online Transaction Processing (OLAP) and Online Analytical Processing (OLAP) describe two distinct use-cases for databases. Some databases may be more suited to one use-case can another. | OLAP | OLTP | | --- | --- | | Analyzes trends | Helps run a business or process | | Read, rarely write data | Read, modify, and often write data | | Contains historical data | Contains current data | | Tens to hundreds of users | Thousands to millions of users | | Size 100GB - 1 TB | Size of several GBs | | Complex queries acting on lots of rows | Simple queries acting on few rows | | Often column-oriented | Generally row-oriented | #### Relational Databases (RDBMS) - Tabular format - Each row is a record with unique id - Each column holds attributes about record - Data integrity constraints - Ensures ACID (atomicity, consistency, isolation, durability) Disadvantages - Difficult to scale horizontally - Single point of failure - Less flexibility in data model Examples: MySQL, PostgreSQL, SQLite, SQL Server #### Non-Relational Databases (NoSQL) | Type | Description | Advantages | Disadvantages | | --- | --- | --- | --- | | Document Store | Stores semistructured data like JSON, BSON, XML | Flexible schema; Easier to scale horizontally | Lax consistency, poor atomicity | | Key Value Store | Stores a map (aka dictionary) usually in memory | Fast storage and retrieval | Constrained set of very simple data structures | | Column Oriented | Stores tables grouped by columns instead of rows | Fast read and aggregation, efficient storage | slow write and update | | Graph Database | Stores a collection of nodes and edges | Useful for modeling social networks, product recommendations | NoSQL databases tend to remove one or more of the constraints from relational databases in favor of flexibility for different use cases. They can be schema-free and built on distributed systems for scalability, but come with other trade offs #### Column-Oriented Databases ${await FileAttachment("截屏2023-02-03 上午10.32.35.png").image()} _{From https://www.heavy.ai/technical-glossary/columnar-database} | Column-Oriented | Row-Oriented | | --- | --- | | Stores and reads data in columns | Stores and reads data in rows | | Efficiently performs operations across all rows (ie. aggregation) | Inefficient at performing operations across all rows | | Slow write and update | Fast write and update | | Slow read on many columns | Fast read on many columns | | Fast and easy to add new columns | Slow to add new columns | | Efficient compression | Inefficient compression | | Suited for OLAP | Suited for OLTP | Examples of a column-oriented DBMS: Clickhouse, Vertica, Paraccel (Actian Matrix and Amazon Redshift), Sybase IQ, Exasol, Infobright, InfiniDB, MonetDB (VectorWise and Actian Vector), LucidDB, SAP HANA, Google Dremel, Google PowerDrill, Druid, and kdb+. #### Read More - https://www.oracle.com/database/what-is-oltp/ - https://www.tutorialspoint.com/dwh/dwh_olap.htm - https://www.oracle.com/database/what-is-a-relational-database/ - https://www.mongodb.com/scale/types-of-nosql-databases - https://www.ibm.com/cloud/blog/sql-vs-nosql - https://www.prisma.io/dataguide/intro/comparing-database-types - http://www.timestored.com/time-series-data/what-is-a-column-oriented-database - https://clickhouse.com/docs/en/intro/ - https://clickhouse.com/docs/en/development/architecture/ - https://www.ionos.com/digitalguide/hosting/technical-matters/graph-database/ - https://dataschool.com/data-modeling-101/row-vs-column-oriented-databases/ ### Why ClickHouse ClickHouse is a **column-oriented** DBMS for **OLAP** using **SQL**. #### The Basics - Good documentation - [Supportive community](https://clickhouse.com/blog/the-click-house-community) - Intuitive [system tables](https://clickhouse.com/docs/en/operations/system-tables/) and [configuration files](https://clickhouse.com/docs/en/operations/configuration-files/) for debug - Can perform well on commodity hardware #### Benchmarks - https://benchmark.clickhouse.com/ - https://tech.marksblogg.com/benchmarks.html #### Read More - [Secrets of ClickHouse Query Performance](https://youtu.be/6WICfakG84c) 31:T41f,--- #### Combine and subtract result sets using `UNION`, `INTERSECT`, and `EXCEPT` ${await FileAttachment("截屏2023-02-03 下午6.12.10.png").image()} [`UNION `](https://clickhouse.com/docs/en/sql-reference/statements/select/union) combines the results of two queries into one table by appending rows rows. `UNION DISTINCT` keeps only unique rows when merging result sets. `UNION ALL` keeps all result rows including duplicates. [`INTERSECT`](https://clickhouse.com/docs/en/sql-reference/statements/select/intersect) keeps the rows that both result sets have in common. `INTERSECT DISTINCT` eliminates duplicate rows. [`EXCEPT`](https://clickhouse.com/docs/en/sql-reference/statements/select/except) returns rows that exist in first set but not the second set > These functions are good for merging tables on the row axis and making the table longer. We'll get to joining tables on the columns axis and making the table wider later. Below we get the 5 most expensive items on the menu and the 5 least expensive items on the menu32:T95a,--- ### Data Types Each column should contain one particular kind of value (ie. a number, a sequence of letters, true or false, a sequence of values, a date, etc). This structure to our schema helps ensure data integrity and makes it easier to read and perform operations on our data. In a column-oriented database, this also allows for efficient data storage via encodings and compression ClickHouse provides many data types to work with but here are the most commonly used. | Category | ClickHouse Data Types | Description | Examples | --- | --- | --- | --- | | Integers | `Int8`, `Int16`, `Int32`, `Int64`, ... | Integers, ${tex`\mathbb{Z}`} | -20, 0, 5 | | Unsigned Integers | `UInt8`, `UInt16`, `UInt32`, `UInt64`, ... | Positive integers and zero (aka the Whole Numbers, ${tex`\mathbb{W}`}) | 0, 5, 10 | | Floating Point Numbers | `Float32`, `Float64` | Numbers including decimal point precision. (aka the Real Numbers, ${tex`\mathbb{R}`}) | 1.0, -2.2 | | Booleans | `Boolean` | True or False | `true`, `false` | | Dates | `Date`, `Date32` | Dates | '2023-02-02' | | DateTimes | `DateTime`, `DateTime64` | The date and time stored together | `'2023-02-03 23:48:25'` | | Strings | `String`, `FixedString(N)` | A sequence of characters | `'Hello World'` | | Arrays | `Array(T)` | A series of values | `[1, 2, 3]`, `['a', 'b', 'c', 'd']` | Note that the number following some of the ClickHouse Data Types' names indicates the number of bits used to store each value. Fewer bits means smaller memory footprint, but also constrains the maximum value that can be stored. For example, a `Date` is stored in 2 bytes or 16 bits, so it can only represent dates from `1970-01-01` until `2149-06-06`. A `Date32` is stored as a signed 32-bit integer and can represent dates from `1900-01-01` until `2299-12-31`. Be careful storing dates outside these ranges as date-typed columns! ClickHouse may incorrectly store out-of-range values. #### Read More - https://clickhouse.com/docs/en/sql-reference/data-types/ --- #### Get a quick overview of a databases, tables, columns, and types with `DESCRIBE TABLE`, `SHOW TABLES`, `SHOW DATABASES` Syntax: `DESCRIBE TABLE ` Shows the name of columns of the table, the type contained in each column, and other info. This is very useful when I forget the exact column_names, information, or formatting that a table I'm working with contains. 33:T52e,___ ### Operators & Functions In addition to retrieving columns, ClickHouse can perform operations on the data in the columns using various operators and functions to act on particular data types. Operators are keywords that act on expressions that are left or right of them. For example the `AND` operator (ie `true AND false` evaluates to `false`). Functions operate on zero or more parameters. For example `lower` is a function that converts `String`s to lowercase. `lower('FOO')` evaluates to `'foo'`. Notice that the parentheses that come after the name of the function contain a comma separated list of the parameters of the function. --- For this section we'll be using the `recipes` table as well as the `numbers(n)` table function. Columns of `recipes` table ${await client.describe('recipes')} Learn about the `numbers(N)` table function [here](https://clickhouse.com/docs/en/sql-reference/table-functions/numbers). #### Read More - [ClickHouse Documentation](https://clickhouse.com/docs/en/sql-reference/functions/). --- #### Operators & Functions for Numbers We had mentioned the numerical comparison operators (`<`, `<=`, `=`, `>=`, `>`, `!=`) earlier. Arithmetic - `plus(a,b)`, `a + b` - `minus(a,b)`, `a - b` - `multiply(a,b)`, `a * b` - `divide(a,b)`, `a / b` - `modulo(a,b)`, `a % b` - `abs(a)` 34:[{"id":"5acf54989c2155c1ad0c6dcb7433fc703e3464572d573c9873dc6d38863339d5e3178453f2ce927068ebe471a97c8e28cfdb1f927513263259c88bdcbfb653ec","href":"https://static.observableusercontent.com/files/5acf54989c2155c1ad0c6dcb7433fc703e3464572d573c9873dc6d38863339d5e3178453f2ce927068ebe471a97c8e28cfdb1f927513263259c88bdcbfb653ec","name":"clickhouse-mark-4238843745.png","type":"image/png","lastModified":1675723684776.506,"size":7715},{"id":"e603fb7726e956e0c0daae8b4636f322284363732fef6761f07262b2350f7b854ebff6a2a5e953de85290da6b96384cab76dc6e8bdc5f0c30c2319914990e7d7","href":"https://static.observableusercontent.com/files/e603fb7726e956e0c0daae8b4636f322284363732fef6761f07262b2350f7b854ebff6a2a5e953de85290da6b96384cab76dc6e8bdc5f0c30c2319914990e7d7","name":"截屏2023-01-21 上午8.39.38.png","type":"image/png","lastModified":1675723684776.506,"size":454070},{"id":"ee7a0edc57bf45e5a284205a3a489e26a0c2528fe50d68436498b8047f99178164b40971cbd8c99ec5c2e9222144f1c2948b3e568852df9698f8a2cb5ad4441b","href":"https://static.observableusercontent.com/files/ee7a0edc57bf45e5a284205a3a489e26a0c2528fe50d68436498b8047f99178164b40971cbd8c99ec5c2e9222144f1c2948b3e568852df9698f8a2cb5ad4441b","name":"截屏2023-02-03 上午10.32.35.png","type":"image/png","lastModified":1675723684776.506,"size":143120},{"id":"dd312090f8617b59263cf90057582ed75e925f7be09daaddb4d03bd5344a6391706b0ca9aab5d2c0afc1dea5f2f66cca75e4026eb54e6f32323aa8d0c95d7ab7","href":"https://static.observableusercontent.com/files/dd312090f8617b59263cf90057582ed75e925f7be09daaddb4d03bd5344a6391706b0ca9aab5d2c0afc1dea5f2f66cca75e4026eb54e6f32323aa8d0c95d7ab7","name":"answers@1.db","type":"application/x-sqlite3","lastModified":1675723684776.506,"size":40960},{"id":"1e70693fe6f506afc9e19ec21d3d0eb06f64a06c508706ac571dbb56fd1c0aee4566920db3a3146e98efbea75c8ee25da135e2a140d71eeec78d0eedced94d81","href":"https://static.observableusercontent.com/files/1e70693fe6f506afc9e19ec21d3d0eb06f64a06c508706ac571dbb56fd1c0aee4566920db3a3146e98efbea75c8ee25da135e2a140d71eeec78d0eedced94d81","name":"截屏2023-02-03 下午6.12.10.png","type":"image/png","lastModified":1675723684776.506,"size":4074367}] 2d:{"title":"ClickHouse SQL Workshop Day 1 - Presenter Edition","cells":[{"id":0,"value":"$2f","pinned":false,"hidden":false,"mode":"md"},{"id":341,"value":"SELECT * FROM stock LIMIT 10","pinned":true,"hidden":false,"mode":"sql","output":"query1","database":"var:client"},{"id":369,"value":"## Exercises\n\nSome cells can check your work and make sure your practice SQL queries are the expected results.\n\nTry changing the query above to `SELECT * FROM stock` (and press CTRL/CMD + ENTER) and the text below will turn red since we are no longer getting only the first 10 rows.","pinned":false,"hidden":false,"mode":"md"},{"id":349,"value":"test(\"Get the first 10 rows of the `stock` table\", () => {\n expect(query1).to.have.lengthOf(10);\n const columns = _.keys(query1[0]);\n expect(columns).to.deep.equal([\n \"symbol\",\n \"date\",\n \"price\",\n \"volume\",\n \"open\",\n \"low\",\n \"high\"\n ]);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":338,"value":"## Topics\n\nThe scope of this tutorial\n\n### What we will be covering\n- Basic SQL syntax (ie. `SELECT`, `INSERT`, `CREATE TABLE`)\n- Common operations on tables (ie. `JOIN`, `UNION`, aggregation)\n- Advanced SQL operations (ie. window functions, views, subqueries)\n- Some useful features specific to ClickHouse\n- Basics of query and database optimization\n\n### What won't be covered\n- Replication & Sharding\n- User Management & Security\n- Workflow Integration (ETL/ELT, Kafka)\n- Serverless Data Warehouse solutions (ClickHouse Cloud, Amazon RedShift, Google BigQuery, etc)\n- Parallelization (Hadoop/Spark, MPP)\n\n### Other great references for ClickHouse\n- [ClickHouse Academy](https://clickhouse.com/learn)\n- [Altinity Webinars](https://youtube.com/playlist?list=PL4OUzcH36K4DTrWiKDqlzbOGJpZvo5uVQ)","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":39,"value":"$30","pinned":false,"hidden":false,"mode":"md"},{"id":50,"value":"## Introduction to SQL Syntax\n\nFor these examples we will use the `trips` table as well as the `menu_item_denorm` table.\n\nThe `trips` table's columns are the following:\n\n${await client.describe('trips')}\n
\nThe `menu_item_denorm` table's columns are as follows:\n\n${await client.describe('menu_item_denorm')}","pinned":false,"hidden":false,"mode":"md"},{"id":315,"value":"### `SELECT ... FROM ...`\n\nTo retrieve columns from a particular table use `SELECT FROM

`\n\nIf there are many columns, separate the column names with commas.\n\nExample:","pinned":false,"hidden":false,"mode":"md"},{"id":324,"value":"SELECT trip_id, pickup_datetime FROM trips","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":388,"value":"#### Exercises","pinned":false,"hidden":false,"mode":"md"},{"id":400,"value":"test(\"Retrieve all rows of the `pickup_datetime` column\", () => {\n checkExerciseAttempted(s1s1e1);\n expect(_.values(s1s1e1[0])).to.have.lengthOf(1);\n expect(_.keys(s1s1e1[0])).to.deep.equal([\"pickup_datetime\"]);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":394,"value":"SELECT pickup_datetime FROM trips","pinned":true,"hidden":false,"mode":"sql","output":"s1s1e1","database":"var:client"},{"id":409,"value":"test(\"Retrieve all rows of the `trip_id`, `trip_distance`, `fare_amount` columns\", () => {\n checkExerciseAttempted(s1s1e2);\n expect(_.keys(s1s1e2[0])).to.deep.equal([\n \"trip_id\",\n \"trip_distance\",\n \"fare_amount\"\n ]);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":411,"value":"SELECT trip_id, trip_distance, fare_amount FROM trips","pinned":true,"hidden":false,"mode":"sql","output":"s1s1e2","database":"var:client"},{"id":429,"value":"---\n#### Select all columns using `*`\n\nTo select all columns of a table, use `*` instead of the list of column names.\n\nThis is generally not recommended because you should only retrieve the columns that you need. Usually you only need a subset of all the columns.","pinned":false,"hidden":false,"mode":"md"},{"id":435,"value":"SELECT * FROM trips","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":601,"value":"To select all columns except one particular column, you can use\n> `EXCEPT ()` ","pinned":false,"hidden":false,"mode":"md"},{"id":607,"value":"SELECT * EXCEPT (trip_id, vendor_id) FROM trips","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":439,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":442,"value":"test(\"Select all columns from the `menu_item_denorm` table\", () => {\n checkExerciseAttempted(s1s2e1);\n expect(_.keys(s1s2e1[0])).to.deep.equal([\"price\",\"high_price\",\"created_at\",\"updated_at\",\"xpos\",\"ypos\",\"dish_id\",\"dish_name\",\"dish_description\",\"dish_menus_appeared\",\"dish_times_appeared\",\"dish_first_appeared\",\"dish_last_appeared\",\"dish_lowest_price\",\"dish_highest_price\",\"menu_id\",\"menu_name\",\"menu_sponsor\",\"menu_event\",\"menu_venue\",\"menu_place\",\"menu_physical_description\",\"menu_occasion\",\"menu_notes\",\"menu_call_number\",\"menu_keywords\",\"menu_language\",\"menu_date\",\"menu_location\",\"menu_location_type\",\"menu_currency\",\"menu_currency_symbol\",\"menu_status\",\"menu_page_count\",\"menu_dish_count\"]\n);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":444,"value":"SELECT * FROM menu_item_denorm","pinned":true,"hidden":false,"mode":"sql","output":"s1s2e1","database":"var:client"},{"id":425,"value":"---\n#### Filter your results with `WHERE`\n\nAdd a `WHERE ` to specify which rows you want. \n\nCommon Logical Operators: `AND`, `OR`, `NOT`\n\nCommon Comparison Operators: `<`, `<=`, `=`, `>=`, `>`, `!=` (not equals), `LIKE`, `BETWEEN`\n\nBelow, we keep only the trips where `cab_type` is yellow and the pickup occurred before January 1st, 2015.\nNotice that the conditions are joined with an `AND`.","pinned":false,"hidden":false,"mode":"md"},{"id":427,"value":"SELECT \n cab_type, pickup_datetime, trip_distance, fare_amount \nFROM trips WHERE \n (cab_type = 'yellow') AND \n (pickup_datetime < '2015-01-01')","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":525,"value":"Below, we get rows with a `fare_amount` between 2 and 9 and a `dropoff_ntacode` with 'N' as the second letter","pinned":false,"hidden":false,"mode":"md"},{"id":527,"value":"SELECT \n fare_amount, dropoff_ntacode, cab_type, trip_distance\nFROM trips\nWHERE \n fare_amount BETWEEN 2 AND 9 AND\n (dropoff_ntacode LIKE '_N%') AND (dropoff_ntacode LIKE 'M%')","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":465,"value":"#### Read More\n- [ClickHouse Comparison Operators](https://clickhouse.com/docs/en/sql-reference/operators/#comparison-operators)\n- [ClickHouse Logical Operators](https://clickhouse.com/docs/en/sql-reference/operators/#logical-and-operator)","pinned":false,"hidden":false,"mode":"md"},{"id":483,"value":"#### Exercises","pinned":false,"hidden":false,"mode":"md"},{"id":485,"value":"test(\"Retrieve the `mta_tax` and `passenger_count` columns for rows that have non-zero `mta_tax`\", () => {\n checkExerciseAttempted(s1s3e1);\n expect(_.keys(s1s3e1[0])).to.deep.equal([\"mta_tax\", \"passenger_count\"]);\n expect(_.every(_.map(s1s3e1, \"mta_tax\"), (x) => x != 0)).to.be.true;\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1159,"value":"_{NOTE: The database somehow has a -0.5 `mta_tax` and the tester used to be checking for no taxes less than or equal to zero. Oops. Fixed the tester to accomodate for the -0.5 value.}","pinned":false,"hidden":false,"mode":"md"},{"id":473,"value":"SELECT mta_tax, passenger_count FROM trips WHERE mta_tax != 0 ","pinned":true,"hidden":false,"mode":"sql","output":"s1s3e1","database":"var:client"},{"id":535,"value":"test(\"Get `trip_id`, `payment_type_` and `dropoff_ntacode` columns for rows with `payment_type_` of CRE and `dropoff_ntacode` that ends with 2 \", () => {\n checkExerciseAttempted(s1s3e2);\n expect(_.keys(s1s3e2[0])).to.deep.equal([\n \"trip_id\",\n \"payment_type_\",\n \"dropoff_ntacode\"\n ]);\n expect(_.every(s1s3e2, (r) => r[\"dropoff_ntacode\"].slice(-1) === \"2\")).to.be\n .true;\n expect(_.every(s1s3e2, (r) => r[\"payment_type_\"] === \"CRE\")).to.be.true;\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":511,"value":"SELECT trip_id, payment_type_, dropoff_ntacode FROM trips WHERE payment_type_ = 'CRE' AND dropoff_ntacode LIKE '%2'","pinned":true,"hidden":false,"mode":"sql","output":"s1s3e2","database":"var:client"},{"id":563,"value":"---\n#### Give a name to result columns with `AS`\n\nSyntax: `AS `\n\nBelow, we alias the `pickup_longitude` column to `longitude` only within our result set, without modifying the underlying table.","pinned":false,"hidden":false,"mode":"md"},{"id":565,"value":"SELECT trip_id, trip_id AS id, vendor_id AS vendor FROM trips;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":572,"value":"---\n#### Find unique values with `DISTINCT`\n\nAdd `DISTINCT` before the column name to retrieve the distinct values of that column. \n\nBelow, we retrieve all types of cab","pinned":false,"hidden":false,"mode":"md"},{"id":574,"value":"SELECT DISTINCT cab_type FROM trips;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":579,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/statements/select/distinct/","pinned":false,"hidden":false,"mode":"md"},{"id":610,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":591,"value":"test(\"List the different payment types found in the `payment_type_` column\", () => {\n checkExerciseAttempted(s1s5e1)\n expect(_.keys(s1s5e1[0])[0]).to.equal(\"payment_type_\");\n expect(_.map(s1s5e1, \"payment_type_\")).to.include(\n \"NOC\",\n \"CRE\",\n \"CSH\",\n \"UNK\",\n \"DIS\"\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":588,"value":"SELECT DISTINCT payment_type_ FROM trips","pinned":true,"hidden":false,"mode":"sql","output":"s1s5e1","database":"var:client"},{"id":599,"value":"---\n#### Sort the rows with `ORDER BY`\n\nSyntax: `ORDER BY `\n\nHere we sort the `menu_item_denorm` table first by highest price, and for items of the same price, we sort by dish name alphabetically.\n","pinned":false,"hidden":false,"mode":"md"},{"id":613,"value":"SELECT dish_name, price FROM menu_item_denorm\n ORDER BY price DESC, dish_name ASC","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":624,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/statements/select/order-by","pinned":false,"hidden":false,"mode":"md"},{"id":628,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":640,"value":"test(\"Get the created_at and dish_name columns with rows sorted such that the more recently created dishes are on top\", () => {\n checkExerciseAttempted(s1s6e1)\n const createdAt = _.map(s1s6e1, \"created_at\");\n expect(\n _.every(createdAt, (v, i, arr) =>\n i + 1 === arr.length ? true : v >= arr[i + 1]\n )\n ).to.be.true;\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":630,"value":"SELECT created_at, dish_name FROM menu_item_denorm ORDER BY created_at DESC","pinned":true,"hidden":false,"mode":"sql","output":"s1s6e1","database":"var:client"},{"id":665,"value":"---\n#### Truncate or paginate your results with `LIMIT` and `OFFSET`\n\nSyntax: `LIMIT OFFSET `\n\nWhen we don't need all potential result rows, it's more efficient to only get a subset of the rows. Subsequent queries can target rows that were previously neglected.\n\nHere we return only 5 rows from the trips table. This is particularly useful to get an idea of the formatting of a table without having to load all the data into memory at once.","pinned":false,"hidden":false,"mode":"md"},{"id":670,"value":"SELECT * FROM trips LIMIT 5","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":673,"value":"Using `OFFSET` we can get the next 3 rows. In other words skip the first five rows and get the 3 rows immediately after.","pinned":false,"hidden":false,"mode":"md"},{"id":676,"value":"SELECT * FROM trips LIMIT 5 OFFSET 3","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":679,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/statements/select/limit\n- https://clickhouse.com/docs/en/sql-reference/statements/select/offset","pinned":false,"hidden":false,"mode":"md"},{"id":681,"value":"#### Exercises","pinned":false,"hidden":false,"mode":"md"},{"id":690,"value":"test('Get three trip_ids', () => {\n checkExerciseAttempted(s1s7e1)\n expect(s1s7e1).to.have.lengthOf(3)\n expect(_.keys(s1s7e1[0])).to.deep.equal(['trip_id'])\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":683,"value":"SELECT trip_id FROM trips LIMIT 3","pinned":true,"hidden":false,"mode":"sql","output":"s1s7e1","database":"var:client"},{"id":719,"value":"$31","pinned":false,"hidden":false,"mode":"md"},{"id":752,"value":"SELECT \n dish_name, \n price \nFROM menu_item_denorm \nORDER BY price DESC \nLIMIT 5\n\nUNION ALL\n\nSELECT\n dish_name,\n price\nFROM menu_item_denorm\nORDER BY price ASC\nLIMIT 5","pinned":false,"hidden":true,"mode":"sql","output":"","database":"var:client"},{"id":745,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/statements/select/union\n- https://clickhouse.com/docs/en/sql-reference/statements/select/intersect\n- https://clickhouse.com/docs/en/sql-reference/statements/select/except","pinned":false,"hidden":false,"mode":"md"},{"id":757,"value":"$32","pinned":false,"hidden":false,"mode":"md"},{"id":955,"value":"DESCRIBE TABLE recipes","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1095,"value":"`SHOW TABLES` and `SHOW DATABASES` do exactly what they say","pinned":false,"hidden":false,"mode":"md"},{"id":1097,"value":"SHOW TABLES","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1100,"value":"SHOW DATABASES","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":800,"value":"$33","pinned":false,"hidden":false,"mode":"md"},{"id":818,"value":"SELECT \n rand(0) AS a, \n rand(1) AS b,\n a + b AS added,\n a - b AS subtracted, \n a * b AS multiplied,\n a / b AS divided,\n a % b AS moded,\n abs(subtracted)\nFROM numbers(10);","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":885,"value":"SELECT \n rand(0) AS a, \n rand(1) AS b,\n a > b AS gt,\n a < b AS lt, \n a = b AS eq,\n a != b AS ne\nFROM numbers(10);","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":808,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/functions/arithmetic-functions\n- https://clickhouse.com/docs/en/sql-reference/functions/random-functions\n- https://clickhouse.com/docs/en/sql-reference/functions/comparison-functions","pinned":false,"hidden":false,"mode":"md"},{"id":840,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":849,"value":"test(\"Find the range of dish prices (high-low) using `dish_highest_price` and `dish_lowest_price` columns of `menu_item_denorm` table. Remove rows where those columns are zero. Get the first thousand results sorted by menu_id and dish_id. Name the result column `range`.\", async () => {\n checkExerciseAttempted(s3s1e1);\n const answer = await answers.query(\"SELECT dish_name, range FROM s3s1e1\");\n expect(s3s1e1).to.deep.equal(answer);\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":836,"value":"SELECT \n dish_name,\n dish_highest_price - dish_lowest_price AS range\nFROM menu_item_denorm \nWHERE dish_highest_price > 0 AND dish_lowest_price > 0\nORDER BY menu_id, dish_id\nLIMIT 1000","pinned":true,"hidden":false,"mode":"sql","output":"s3s1e1","database":"var:client"},{"id":892,"value":"---\n#### Operators and Functions For Strings\n\nOperators\n- `concat(a,b)`, `a || b` -- combine two strings\n- `a LIKE b` -- TRUE if String matches a pattern, b.\n- `a = b` -- TRUE if String a is the same as String b\n\n[(There are more sophisticated search functions if necessary)](https://clickhouse.com/docs/en/sql-reference/functions/string-search-functions) \n\nThere are a multitude of useful functions for working with strings. I'll cover a few I find very useful. \n\n- `empty(x)` - TRUE if String is empty. Otherwise FALSE.\n- `length(x)` - Length of String\n- `trim(x)` - Remove leading and trailing blank space.\n- `substring(s, offset, length)` - Extract part of a string\n","pinned":false,"hidden":false,"mode":"md"},{"id":898,"value":"SELECT \n title, \n source,\n length(title),\n concat(source, '-', trim(title)),\n lower(title) LIKE '%chocolate%'\nFROM recipes","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":915,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/functions/string-functions\n- https://clickhouse.com/docs/en/sql-reference/functions/string-search-functions\n- https://clickhouse.com/docs/en/sql-reference/functions/string-replace-functions","pinned":false,"hidden":false,"mode":"md"},{"id":922,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":924,"value":"test(\"Get all values from the `link` column of the `recipes` table and add 'https://' to the beginning of each link. Name this the `answer` column.\", () => {\n checkExerciseAttempted(s3s2e1);\n expect(\n _.every(_.map(s3s2e1, \"answer\"), (str) => str.substr(0, 8) === \"https://\")\n );\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":927,"value":"SELECT \n concat('https://', link) AS answer\nFROM recipes","pinned":true,"hidden":false,"mode":"sql","output":"s3s2e1","database":"var:client"},{"id":963,"value":"___\n#### Date and Time functions\n\n- `now()` -- Get the current date and time\n- `today()` -- Get the current date\n- `yesterday()` -- Get yesterday's date\n- `dateDiff(unit, start, end)` -- subtract datetimes in terms of a unit (second, minute, hour, day, week, month, quarter, year)\n- `addYears`, `addMonths`, `addWeeks`, `addDays`, `addHours`, `addMinutes`, `addSeconds`, `addQuarters`\n- `subtractYears`, `subtractMonths`, `subtractWeeks`, `subtractDays`, `subtractHours`, `subtractMinutes`, `subtractSeconds`, `subtractQuarters`\n- `formatDateTime(datetime, format[, timezone])` - Return a string containing the date time formatted in a way you specify","pinned":false,"hidden":false,"mode":"md"},{"id":970,"value":"SELECT\n now(),\n today(),\n dateDiff('hour', yesterday(), today()) AS hrs_btn_td_ytd,\n today() + INTERVAL 1 day AS tomorrow,\n today() - INTERVAL 1 year AS yester_year,\n formatDateTime(now(), '%D at %I:%M:%S %p', 'EST')","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":967,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions","pinned":false,"hidden":false,"mode":"md"},{"id":1154,"value":"---\n\n## PAUSED HERE ON 2023-02-06","pinned":false,"hidden":false,"mode":"md"},{"id":986,"value":"---\n### Convert between data types using `CAST`\n\nSometimes you need to treat numbers as Strings to add commas, dollar signs, and percent signs. Sometimes you need to treat Strings as Dates. Sometimes you need to convert booleans to numbers. `CAST` will tell ClickHouse to treat one data type as another.\n\nSyntax: \n- `CAST( AS )` \n- `CAST(, '')` \n- `::type`\n\nThere are also many more specific conversion functions such as `toDate32` and `toString`. [See documentation](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions)","pinned":false,"hidden":false,"mode":"md"},{"id":990,"value":"SELECT\n CAST(1234 AS String) AS my_string,\n CAST('1970-01-01' AS Date) AS my_date,\n CAST('1899-01-01' AS Date) AS my_wrong_date,\n CAST(0 AS BOOLEAN) AS my_false,\n CAST(1 AS BOOLEAN) AS my_true\n","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":998,"value":"#### Read More\n- https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions/#castx-t","pinned":false,"hidden":false,"mode":"md"},{"id":1013,"value":"#### Exercise","pinned":false,"hidden":false,"mode":"md"},{"id":1015,"value":"test(\"Add a percent sign to the end of each of the numbers below. Name the resulting column `answer`.\", () => {\n if (!(\"answer\" in s4s1e1[0])) throw new UnattemptedExerciseError();\n expect(_.every(s4s1e1, (r) => typeof r.answer === \"string\")).to.be.true;\n expect(_.every(s4s1e1, (r) => r.answer.slice(-1))).to.be.true;\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1010,"value":"SELECT number FROM numbers(10)","pinned":true,"hidden":false,"mode":"sql","output":"s4s1e1","database":"var:client"},{"id":1001,"value":"---\n### Conditional logic using `if(cond, then, else)`, `CASE WHEN` Expression, and the Ternary Operator\n\n- `if(cond, then, else)`\n- `cond ? val_if_true : val_if_false` -- aka the ternary operator\n- ```sql\n CASE x\n WHEN a THEN b\n WHEN ... THEN ...\n ELSE c\n END\n ```","pinned":false,"hidden":false,"mode":"md"},{"id":1041,"value":"SELECT\n title, \n length(ingredients) > 3 ? 'four or more ingredients' : 'three or less ingredients' AS category,\n CASE source\n WHEN 'Gathered' THEN true\n ELSE false\n END AS was_gathered\nFROM recipes;","pinned":true,"hidden":false,"mode":"sql","output":"","database":"var:client"},{"id":1032,"value":"#### Read More\n\n- https://clickhouse.com/docs/en/sql-reference/operators/#conditional-expression\n- https://clickhouse.com/docs/en/sql-reference/functions/conditional-functions","pinned":false,"hidden":false,"mode":"md"},{"id":1060,"value":"---\n### Get the results in your desired output format using `FORMAT`\n\nClickHouse supports a vast variety of input and output formats. See the list [here](https://clickhouse.com/docs/en/sql-reference/formats). It uses `TabSeparated` by default (`PrettyCompat` if you're using clickhouse-client interactively). \n\nSyntax: `FORMAT `\n\n```sql\nSELECT * FROM trips WHERE cab_type = 'green' LIMIT 10 FORMAT CSVWithNames;\n```\n\nOutput:\n```csv\n${await fetch(\n \"https://play.clickhouse.com/?add_http_cors_header=1&user=play&default_format=JSON\",\n {\n method: \"POST\",\n body: \"SELECT * FROM trips WHERE cab_type = 'green' LIMIT 10 FORMAT CSVWithNames;\"\n }\n ).then((res) => res.text())\n}\n```\n\n#### Read More\n- [List of formats](https://clickhouse.com/docs/en/sql-reference/formats)\n- https://clickhouse.com/docs/en/sql-reference/statements/select/format/","pinned":false,"hidden":false,"mode":"md"},{"id":1088,"value":"---\n### Save to file with `INTO OUTFILE`\n\n`INTO OUTFILE file_name [COMPRESSION type]` saves the results into a file on the **client** side. It's able to infer compression type based on file extension or you can input it explicitly.\n\n```sql\nSELECT \n * \nFROM trips \nWHERE cab_type = 'green' \nLIMIT 10 \nINTO OUTFILE 'my-result.csv.gz'\nFORMAT CSVWithNames;\n```\n\nAlternatively, you can redirect output from your command-line process into a file. For example:\n\n```bash\n$ clickhouse-client --query \"SELECT * FROM table\" --format CSVWithNames > output.csv\n```\n\n#### Read More\n- https://clickhouse.com/docs/en/faq/integration/file-export/\n- https://clickhouse.com/docs/en/sql-reference/statements/select/into-outfile/","pinned":false,"hidden":false,"mode":"md"},{"id":94,"value":"## Appendix","pinned":false,"hidden":false,"mode":"md"},{"id":5,"value":"import { client } 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":867,"value":"answers = FileAttachment(\"answers@1.db\").sqlite()","pinned":false,"hidden":false,"mode":"ojs"},{"id":87,"value":"import { test, expect } 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"}],"files":"$W34","stdlib":"1","titling":"auto"}