What Is Snowflake?<\/h2>\n
Snowflake is a cutting-edge cloud-based data warehousing platform that stands out for its ability to efficiently store and analyse vast volumes of data. What sets Snowflake apart is its innovative architecture, which effectively separates storage and compute operations. Unlike traditional database solutions like Teradata and Oracle, Snowflake eliminates the need for installing, configuring, and managing hardware or software. It operates entirely on public cloud infrastructure. However, what truly distinguishes Snowflake and makes it unparalleled is its exceptional architecture and robust data sharing capabilities.<\/p>\n
At its core, Snowflake Data Cloud is a fully managed, cloud-native solution that allows users to store and manage structured and semi-structured data from various sources in a central location. With Snowflake, organizations can scale up or down as needed to meet changing demand, without worrying about infrastructure or maintenance.<\/p>\n
Moreover, Snowflake is available on multiple cloud platforms, including AWS, Azure, and Google Cloud Platform, providing users with the flexibility to choose the cloud provider that best suits their needs. In traditional databases compute and storage resources are limited. In those databases, increases in storage and compute resources must be applied together, as they are tightly coupled.<\/p>\n
Snowflake solves the problem of tight coupling: customers can add compute and storage to their data warehouses independently from each other, and storage is virtually unlimited. You pay for what you use, and you don\u2019t need to manage any hardware or software, as it is all fully managed by snowflake.<\/p>\n
Snowflake is an incredibly versatile platform that excels in numerous data-related domains, including data warehousing, data engineering, data lakes, data science, and data application development. It seamlessly integrates with popular visualization tools like Tableau and Power BI, allowing users to create visually stunning and interactive dashboards. These dashboards provide a comprehensive view of data, enabling users to easily identify trends, patterns, and anomalies. With Snowflake’s integration capabilities, organizations can unlock the full potential of their data and make data-driven decisions with enhanced clarity and understanding.<\/p>\n
How Can You Access Snowflake?<\/h2>\n
To access Snowflake, open your browser to https:\/\/www.snowflake.com<\/a>. To access a free 30-day trial, no credit card required, just provide your personal details, and choose a cloud provider, and your account will be ready within minutes. The free trial is a great option for initial learning. I won\u2019t go into more detail about the free trial setup here, but I can share more in the comments if anyone requires. However, it is very easy to set up.<\/p>\n In this section I will share some of the ways that Snowflake’s architecture is unique (or certainly not common to most database management systems.<\/p>\n The Snowflake architecture represents a new architecture that was designed and built from the ground up. It is considered a hybrid of traditional shared-disk and shared-nothing database architectures, and it does not copy any traditional database architecture.<\/p>\n Figure 1 shows a high-level view of the Snowflake architecture.<\/p>\n Figure 1: Architecture of snowflake<\/strong><\/p>\n As shown in Figure 1, the unique Snowflake architecture consists of three key layers:<\/p>\n This is the \u201cbrain\u201d of Snowflake where activities such as authentication, infrastructure management, metadata management, query parsing and optimization, and access control are all managed. These services tie together all the different components of Snowflake to process user requests, from login to query dispatch.<\/p>\n Query Processing<\/strong><\/p>\n This layer is also known as Compute layer. Considered the \u201cbrawn\u201d of Snowflake, the Query Processing layer performs data manipulations, transformations, and queries (data fetching) requested by users. All this processing is done by Snowflake using virtual warehouses. Here are some benefits of virtual warehouses:<\/p>\n Snowflake is a columnar database. When data is loaded into Snowflake, it is internally reorganized into an optimized, compressed, columnar format. Snowflake stores this optimized data in Cloud storage. Snowflake’s database storage layer is tasked with the responsibility of storing and managing data. It utilizes a scalable and elastic storage infrastructure, effectively decoupling compute, and storage operations. The data is organized into numerous micro-partitions, optimizing data access and query performance. Notably, Snowflake’s storage layer exhibits a high level of durability, automatically replicating data across multiple availability zones to ensure reliability. Moreover, it is designed to seamlessly handle extensive data volumes and supports a wide array of data formats, enabling organizations to efficiently store and analyse diverse data types.<\/p>\n The internal representation of data objects is not accessible, nor is it visible, to customers. Snowflake manages all aspects of storage file organization, metadata compression, and data storage statistics. This ensures that users do not need to worry about data distribution across different micro partitions.<\/p>\n As the storage layer is independent, customers need to pay only for the average monthly storage used. Since Snowflake is provisioned on the Cloud, storage is elastic and is charged as per terabyte (TB) of usage per month. Compute nodes connect with the Database Storage layer to fetch data for query processing. The main benefit of columnar databases store is it stores data in columns rather than rows, they are well-suited for analytical workloads that involve querying large amounts of data. Columnar databases can retrieve data more quickly than row-oriented databases because they only need to read the columns that are required for a particular query. This can result in significant performance improvements, particularly for complex queries that involve multiple joins and aggregations.<\/p>\n Now let\u2019s discuss how to create virtual warehouses. By default, when we create a Snowflake account it comes with a default warehouse named To create a new warehouse, log into the Snowflake web UI, go to the Warehouses tab and click the \u201c+ Warehouse\u201d button. Give your new warehouse a meaningful name. I have named my new warehouse Figure 2: how to create a virtual warehouse from snowflake UI.<\/strong><\/p>\n A warehouse is created within a few seconds, giving us a glimpse of the power of Cloud computing and Snowflake. We did not purchase any hardware, performed no installation, and yet we now have a data warehouse up and running within minutes. The main advantage is we can change the compute power on the fly, have multiple warehouses\u2014like one for each department\u2014and only pay for the resources we use. Figure 3 shows the Warehouses summary screen after we create our warehouse.<\/p>\n Figure 3: Virtual warehouse window after creation.<\/strong><\/p>\n You can also create warehouse using the Snowflake Execute this code and you should see:<\/p>\n Figure 4: SQL\u2019s and parameters for creating virtual warehouse.<\/strong><\/p>\n You can find the full syntax and additional options related to virtual warehouse creation at the following link: https:\/\/docs.snowflake.com\/en\/sql-reference\/sql\/create-warehouse.html<\/a><\/strong><\/p>\n The Web UI provides access to additional features like transferring ownership, roles, and many other warehouse management functions. In the Warehouse Activity tab, you can see individual query execution metrics like executed query text, query executor, and duration, etc. Figure 5 shows the Warehouse Activity screen.<\/p>\n Figure 5: Web UI demo for available information.<\/strong><\/p>\n Time travel in Snowflake enables users to retrieve historical versions of data that were previously stored in the system. This feature allows users to query the data as it appeared at a specific moment in time, regardless of any subsequent changes or deletions that may have occurred.<\/p>\n For more details on Snowflake time travel, see the following documentation:<\/p>\n https:\/\/docs.snowflake.com\/en\/user-guide\/data-time-travel<\/a><\/p>\n Within the context of Snowflake, the term “fail-safe” pertains to the system’s capability to prevent data loss, corruption, or compromise in the event of a system failure or outage. The fail-safe mechanisms implemented in Snowflake are intended to uphold data integrity and to reduce the possibility of any downtimes or data loss.<\/p>\n For more details on Snowflake fail-safe, see the following documentation:<\/p>\n https:\/\/docs.snowflake.com\/en\/user-guide\/data-failsafe<\/a><\/p>\n In this section, I will cover some of characteristics of Snowflake tables to help you understand the basic architecture of how tables are built and utilized.<\/p>\n Snowflake provides 3 different types of tables: Temporary, Transient, and Permanent.<\/p>\n Temporary tables are like permanent tables with a caveat that they only exist for the current session. A temporary table is only visible to the user who created it and cannot be accessed by anyone else. When the session ends, the data in the temporary table gets purged.<\/p>\n The common use case for temporary tables is storing data, which is non-permanent and can be recreated, like aggregates, etc. Temporary tables serve as a staging area for data prior to undergoing transformations or being loaded into permanent tables. They are handy for storing intermediate results during complex data processing tasks and can improve query performance by materializing intermediate results.<\/p>\n Figure 6 shows the creation of a temporary table.<\/p>\n Figure 6: Code for creating a temporary table. <\/strong><\/p>\n You can find the full syntax and additional options related to create table creation at the following link: https:\/\/docs.snowflake.com\/en\/sql-reference\/sql\/create-table<\/a>.<\/p>\n Transient tables operate like permanent tables except for the fact that they do not have a fail-safe period associated with them. The maximum time travel retention period for transient tables is one day, while permanent tables in the Enterprise Edition or higher can retain data for up to 90 days. Data in the transient table is persisted until the table is explicitly dropped, so unlike a temporary table the transient table will remain even after the session it was created in ends.<\/p>\n There is no fail-safe cost associated with it, but it does contribute to overall storage expenses for accounts.<\/p>\n Figure 7 shows how to create a transient table.<\/p>\n Figure 7: Code to create a transient table. <\/strong><\/p>\n Permanent tables have a fail-safe period and provide an additional level of data recovery and protection. A permanent table is created when data must be stored for a longer period. Permanent tables persist until explicitly dropped. Figure 8 shows the creation of a permanent table.<\/p>\n <\/p>\n Figure 8: Web UI creating a permanent table. <\/strong><\/p>\n Inserting data into This will insert the following data as shown in Figure 9:<\/p>\n Figure 9: Web UI showing inserted data. <\/strong><\/p>\n Table 1 summarizes the differences between Snowflake table types and their impact on time travel and fail-safe functionality.<\/p>\n Table Type<\/strong><\/p>\n<\/td>\n Persistence<\/strong><\/p>\n<\/td>\n Time\u00a0Travel\u00a0Retention\u00a0Period\u00a0(Days)<\/strong><\/p>\n<\/td>\n Fail-safe\u00a0Period\u00a0(Days)<\/strong><\/p>\n<\/td>\n<\/tr>\n Temporary<\/p>\n<\/td>\n Remainder of session<\/p>\n<\/td>\n 0 or 1 (default is 1)<\/p>\n<\/td>\n 0<\/p>\n<\/td>\n<\/tr>\n Transient<\/p>\n<\/td>\n Until explicitly dropped<\/p>\n<\/td>\n 0 or 1 (default is 1)<\/p>\n<\/td>\n 0<\/p>\n<\/td>\n<\/tr>\n Until explicitly dropped<\/p>\n<\/td>\n 0 or 1 (default is 1)<\/p>\n<\/td>\n 7<\/p>\n<\/td>\n<\/tr>\n Permanent (Enterprise Edition and higher)<\/a><\/p>\n<\/td>\n Until explicitly dropped<\/p>\n<\/td>\n 0 to 90 (default is configurable)<\/p>\n<\/td>\n 7<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Table 1. Differences Between Snowflake Table Types.<\/p>\n In this section we will consider clustering keys in Snowflake and their effect on query performance for large tables.<\/p>\n The clustering key in Snowflake is a designated column or set of columns that dictates the physical storage order of data within a table. It plays a crucial role in determining how data is structured and grouped together on disk, leading to notable effects on query performance and the efficiency of data retrieval. By carefully selecting the clustering key, you can optimize the organization of data on disk, resulting in improved query performance and faster access to relevant information.<\/p>\n Fig. 14 shows a cluster table where data with same month in kept in same micro partitions.<\/p>\n Within Snowflake’s architecture, micro-partitions refer to individual units of data storage that comprise a section of a table’s data. These micro-partitions are designed to contain a relatively small amount of data, usually ranging between 50 MB to 500 MB. Moreover, data stored within each micro-partition is compressed, columnar-organized, and saved in a specialized format, which is fine-tuned to optimize query processing performance.<\/p>\n Clustering in snowflake relates to arrangement of rows co-located with similar rows in the same micro-partitions. The effect of clustering can be seen for very large tables (larger than 1+ TB) when ordering of data when initially loaded data was not ordered or an excessive number of Data Manipulation Language (DML) operations have been performed against the table (like When a clustering key is defined on a table, the automatic clustering service will co-locate rows with similar values in their cluster key columns to the same micro-partitions.<\/p>\n The Benefits of Defining Clustering Keys:<\/strong><\/p>\n A clustering key can be defined on a table at the time of creation or by using the To demonstrate, we will create a table without any clustering key and insert data in it, using the following code. The results of this query are shown in Figure 15.<\/p>\n This will return:<\/p>\n Figure 15: Snowflake web UI showing SQL run of query.<\/strong><\/p>\n We can then select a record from our sample table using a SELECT query with a WHERE clause filter, as shown in the following code:<\/p>\n The output is shown in Figure 16.<\/p>\n Figure 16. SELECT from sample non-clustered table.<\/strong><\/p>\n Here we will look at the power of Snowflake\u2019s semi-structured data handling capability. We will create a table with the For example:<\/p>\n Insert below test data for loading into table.<\/p>\n Figure 10: Running SQL and INSERT query in Snowflake web UI.<\/strong><\/p>\n Once our Executing this code will return:<\/p>\n Figure 11: Showing data in Snowflake web UI.<\/strong><\/p>\n Now we\u2019ll consider the technique for parsing the Below are some pre-defined functions which can be used with variant data.<\/p>\n Sample query <\/strong><\/p>\n Using Snowflake\u2019s semi-structured data extraction operators, our Figure 12:SQL for parsing JSON in Snowflake web UI.<\/strong><\/p>\n Figure 13 shows the results of our JSON extraction query.<\/p>\n Figure 13: Flattened semi structured JSON data.<\/strong><\/p>\n To look as the query plan of queries that have been executed. You<\/p>\n can go to query profiling page through activity tab \ud83e\udc6a Query History<\/strong>:<\/p>\n Figure 17: query history tab <\/strong><\/p>\n Select the query for which you want to see profile.<\/p>\n Now when we look at the query profile, we notice this table had 23 micro-partitions, and all 23 were scanned while searching for records where Figure 18: Snowflake web UI showing query profile result for table with no clustering key.<\/strong><\/p>\n Now let\u2019s create an equivalent table with a clustering key and assess how same select query works. The query in Figure 19 creates our new table with a clustering key.<\/p>\n Figure 19: DDL and SQL for execution in snowflake web UI<\/strong><\/p>\n Now run the equivalent <\/p>\n When we look at the query profile, we can clearly see that with a clustering key enabled on the Figure 20: Snowflake web UI showing query profile result for table with clustering key.<\/strong><\/p>\n Within most traditional database management systems (DBMSs), utilization of the Nevertheless, this characteristic distinguishes Snowflake from most other DBMSs. When using Snowflake, the This guarantee of ordering can be attributed to Snowflake’s unique architecture. Its micro-partitioning technique and query optimization methods permit highly efficient and parallelized query execution, resulting in ordered outputs even when using the In this article we considered the basic differences between an on-premises infrastructure and a Cloud-based infrastructure. We then discussed the Snowflake architecture and created a new virtual warehouse. We discussed the different types of tables Snowflake supports and created each type of table. We also covered how to load semi-structured data in Snowflake in a Snowflake Documentation: <\/strong>https:\/\/docs.snowflake.com\/en\/<\/a><\/p>\n Here are a few popular Cloud providers you can visit to get additional information. There are many more, and this article does not endorse any specific one. The links are for informational purposes only.<\/p>\n Amazon Web Services:<\/strong> https:\/\/aws.amazon.com\/<\/a><\/p>\n Microsoft Azure:<\/strong> Cloud Computing Services | Microsoft Azure<\/a><\/p>\nWhat Is Unique About the Snowflake Architecture?<\/h2>\n
![\"Diagram\n\nDescription](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/diagram-description-automatically-generated.png\")
<\/p>\nCloud Services<\/h3>\n
\nEach virtual warehouse is a massively parallel processing (MPP) compute resource cluster. Each virtual warehouse consists of multiple nodes with CPU and memory automatically provisioned in the Cloud by Snowflake.<\/p>\n\n
Database Storage Layer<\/h3>\n
Creating a Virtual Warehouse<\/h2>\n
COMPUTE_WH<\/code>. You can create new warehouses at any time using the ACCOUNTADMIN<\/code> and SYSADMIN<\/code> security roles in Snowflake.<\/p>\nDEMO_WAREHOUSE<\/code> and kept the size as X-Small<\/code>. You can also select Advanced Settings<\/strong> like Auto Resume<\/strong> and Auto Suspend<\/strong> from this New Warehouse<\/strong> dialog. Figure 2 shows the New Warehouse dialog.<\/p>\n![\"Graphical](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/graphical-user-interface-application-description.png\")
<\/p>\n![\"Graphical](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/graphical-user-interface-application-table-exce.png\")
<\/strong><\/p>\nSQL CREATE WAREHOUSE<\/code> statement, shown in Figure 4.<\/p>\nCREATE OR REPLACE WAREHOUSE DEMO_WAREHOUSE\r\nWITH WAREHOUSE_SIZE = XSMALL\r\n COMMENT = 'This is for demo purpose only.'\r\n AUTO_SUSPEND = 300\r\n WAREHOUSE_TYPE = STANDARD;<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97031-4.png\")
<\/p>\n![\"Graphical](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/graphical-user-interface-application-table-desc.png\")
<\/strong><\/p>\nTime travel and fail safe in Snowflake:<\/h3>\n
Snowflake Tables<\/h2>\n
Table Types<\/h3>\n
Temporary Tables<\/h4>\n
CREATE OR REPLACE TEMPORARY TABLE DEMO_TEMP\r\n(\r\nNAME VARCHAR(100),\r\nEMPLOYER VARCHAR(100),\r\nID NUMBER\r\n); <\/pre>\n
Transient Tables<\/h4>\n
CREATE OR REPLACE TRANSIENT TABLE DEMO_TEMP\r\n(\r\nNAME VARCHAR,\r\nEMPLOYER VARCHAR,\r\nID NUMBER\r\n); <\/pre>\n
Permanent Tables<\/h4>\n
CREATE OR REPLACE TABLE DEMO_PERM\r\n(\r\nNAME VARCHAR(100),\r\nEMPLOYER VARCHAR(100),\r\nID NUMBER\r\n);<\/pre>\n
DEMO_PERM<\/code> table:<\/p>\nINSERT INTO DEMO_PERM \r\nVALUES ('Pankaj Gupta','Test Employer','1');\r\nINSERT INTO DEMO_PERM \r\nVALUES ('Ravi Jain','DataDog','2');<\/pre>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97031-6.png\")
<\/strong><\/p>\n\n\n
\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n Clustering Keys and Query Performance<\/h3>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/a-picture-containing-text-screenshot-number-fon.png\")
<\/p>\nINSERT<\/code>, UPDATE<\/code>, etc.), causing the table\u2019s natural clustering to degrade over a period.<\/p>\n\n
ALTER TABLE<\/code> statement if the table already exists.<\/p>\nCREATE OR REPLACE TABLE DEMO_NO_CLUSTERING (\r\nEMP_ID NUMBER(10,0),\r\nDEPT_ID NUMBER(4,0),\r\nEMP_NAME VARCHAR(4),\r\nHOME_ADDR VARCHAR(1000)\r\n);\r\nINSERT OVERWRITE INTO DEMO_NO_CLUSTERING\r\nSELECT\r\nseq4() AS EMP_ID,\r\nuniform(1, 1000, random()) AS DEPT_ID,\r\nrandstr(4, random()) AS EMP_NAME,\r\nrandstr(1000, random()) AS HOME_ADDR\r\nFROM table(generator(rowcount => 550000));<\/pre>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/a-screenshot-of-a-graph-description-automatically.png\")
<\/strong><\/p>\nSELECT * \r\nFROM DEMO_NO_CLUSTERING \r\nWHERE DEPT_ID = 1;<\/pre>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/a-screenshot-of-a-computer-description-automatica.png\")
<\/p>\nSemi-Structured Data in Snowflake<\/h2>\n
VARIANT<\/code> data type and use it to load semi-structured JSON<\/code> data. Figure 10 shows the creation of a table with a VARIANT<\/code> data type column and loading JSON<\/code> data into it.<\/p>\nCREATE OR REPLACE TABLE DEMO_TEMP_VARIANT\r\n(\r\n LOCATION VARIANT\r\n);<\/pre>\n
INSERT INTO DEMO_TEMP_VARIANT \r\nSELECT parse_json(column1)\r\n from values\r\n ('{\r\n \"id\": \"1234\",\r\n \"name\": {\r\n \"first\": \"Pankaj\",\r\n \"last\": \"Gupta\"\r\n },\r\n \"company\": \"demo_company\",\r\n \"email\": \"pankaj.gupta@demo_company.info\",\r\n \"phone\": \"+1 (123) 456-7890\",\r\n \"address\": \r\n \"268 Havens Place, Dunbar, Rhode Island, 7725\"\r\n }')\r\n , ('{\r\n \"id\": \"5678\",\r\n \"name\": {\r\n \"first\": \"Arpan\",\r\n \"last\": \"Rai\"\r\n },\r\n \"company\": \"DIGIGEN\",\r\n \"email\": \"Arpan.Rai@DIGIGEN.net\",\r\n \"phone\": \"+1 (111) 222-3021\",\r\n \"address\": \r\n \"441 Dover Street, Ada, New Mexico, 5922\"\r\n }');<\/pre>\nJSON<\/code> data has been inserted into the Snowflake table, we can view it with a SELECT<\/code> query, as shown in Figure 11.<\/p>\nSELECT * \r\nFROM DEMO_TEMP_VARIANT;<\/pre>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/a-screenshot-of-a-computer-description-automatica-1.png\")
<\/p>\nJSON<\/code> data in this table. One thing to keep in mind is that VARIANT<\/code> values are not strings; VARIANT<\/code> values contain strings. The operators \u201c:\u201d and subsequent \u201c.\u201d and \u201c[]\u201d always return VARIANT<\/code> values containing strings.<\/p>\n\n
PARSE_JSON<\/code> – pre-defined function in Snowflake that facilitates the extraction of data from strings that are formatted in JSON<\/code>. This function takes a solitary parameter, which is a string that contains the data formatted as JSON<\/code>, and subsequently returns a variant data type that can be used in SQL queries.<\/li>\nCHECK_JSON<\/code> built-in function in Snowflake that validates whether a string contains valid JSON<\/code> data. This function can be used to check if a given string is formatted correctly as JSON<\/code> and returns a Boolean value of TRUE<\/code> or FALSE<\/code><\/li>\n<\/ul>\nSELECT CHECK_JSON( \r\n'\r\n{\r\n \"id\": \"1234\",\r\n \"name\": {\r\n \"first\": \"Pankaj\",\r\n \"last\": \"Gupta\"\r\n }\r\n }')\r\nas is_valid;<\/pre>\nJSON<\/code> data can simply be fetched in a flat table format using a simple query like the one shown in Figure 12.<\/p>\nSELECT\r\n (LOCATION:id) as id,\r\n (LOCATION:name:first) as first_name,\r\n (LOCATION:name:last) as last_name,\r\n (LOCATION:company) as company,\r\n (LOCATION:email) as email,\r\n (LOCATION:phone) as phone,\r\n (LOCATION:address) as address\r\nFROM\r\n DEMO_TEMP_VARIANT;<\/pre>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/a-screenshot-of-a-computer-description-automatica-2.png\")
<\/p>\nQuery History and Profiling<\/h2>\n
![\"Graphical](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/graphical-user-interface-text-application-descr.png\")
<\/strong><\/p>\nDEPT_ID = 1<\/code>. This result is shown in Figure 18.<\/p>\n![\"Graphical](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/graphical-user-interface-description-automaticall.png\")
<\/strong><\/p>\nCREATE OR REPLACE TABLE DEMO_CLUSTERING \r\n cluster by (DEPT_ID) (\r\nEMP_ID NUMBER(10,0),\r\nDEPT_ID NUMBER(4,0),\r\nEMP_NAME VARCHAR(4),\r\nHOME_ADDR VARCHAR(1000)\r\n);\r\n \r\nINSERT OVERWRITE INTO DEMO_CLUSTERING\r\nSELECT\r\nseq4() AS EMP_ID,\r\nuniform(1, 1000, random()) AS DEPT_ID,\r\nrandstr(4, random()) AS EMP_NAME,\r\nrandstr(1000, random()) AS HOME_ADDR\r\nFROM table(generator(rowcount => 550000)) \r\nORDER BY DEPT_ID;<\/pre>\n
SELECT<\/code> query from our previous example against the new table, as shown.<\/p>\nSELECT *\r\nFROM DEMO_CLUSTERING\r\nWHERE DEPT_ID = 1;<\/pre>\n
DEPT_ID<\/code> column, this query scans only 1 partition of the 23 available. If there are thousands of partitions, then clustering key can increase CPU resource efficiency and result in more efficient execution. The key thing here is that data should be inserted for the clustering key in the sorted order in our table. This is why we apply the ORDER BY<\/code> clause to the SELECT<\/code> query that feeds our INSERT<\/code> statement. If you do not load data in sorted order for DEPT_ID<\/code>, it will not organize micro-partitions properly and will still scan all the micro-partitions. Figure 20 shows our query profile after applying a clustering key and inserting our data into the table in clustering key order.<\/p>\n![\"Graphical](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/graphical-user-interface-description-automaticall-1.png\")
<\/strong><\/p>\nORDER BY<\/code> clause within a SELECT<\/code> query that feeds a DML statement, such as UPDATE<\/code> or DELETE<\/code>, does not inevitably guarantee ordering of the resulting data. Consequently, rows retrieved by such a query might not consistently maintain the same order every time the query is executed, even if the ORDER BY<\/code> clause is used.<\/p>\nORDER BY<\/code> clause in a SELECT<\/code> query that feeds a DML statement does guarantee ordering of the resulting data. Hence, rows returned by the query will persistently maintain the same order, regardless of how many times the query is executed.<\/p>\nORDER BY<\/code> clause in a DML statement.<\/p>\nSummary<\/h2>\n
VARIANT<\/code> data type column, and how to extract JSON<\/code> data in a tabular format. Finally, we discussed clustering keys and performed a practical demonstration that showed the difference between tables created with and without a clustering key.<\/p>\nReferences<\/h2>\n