# TiDB Cloud Lake > TiDB Cloud Lake is a cloud-native data warehouse service for analytics workloads. It separates compute and storage, and supports ANSI SQL, semi-structured data processing, and AI-oriented workflows. ## Get Started - [Overview](https://docs.pingcap.com/tidbcloudlake/lake-overview.md): TiDB Cloud Lake is a cloud-native data warehouse service for analytics workloads. It separates compute and storage, and supports ANSI SQL, semi-structured data processing, and AI-oriented workflows. - [Quick Start](https://docs.pingcap.com/tidbcloudlake/lake-quick-start.md): Get started with TiDB Cloud Lake in three steps — sign up, initialize your lake, and explore your lake workspace. ## Guides - Connect - [Overview](https://docs.pingcap.com/tidbcloudlake/connection-overview.md): TiDB Cloud Lake supports multiple connection methods to suit different use cases. All options below work with both **TiDB Cloud Lake** and **self-hosted TiDB Cloud Lake**. - SQL Clients - [LakeSQL](https://docs.pingcap.com/tidbcloudlake/connect-using-lakesql.md): LakeSQL is a command line tool that has been designed specifically for TiDB Cloud Lake. It allows users to establish a connection with TiDB Cloud Lake and execute queries directly from a CLI window. - Drivers - [Overview](https://docs.pingcap.com/tidbcloudlake/driver-overview.md): An overview of the official drivers available for connecting to TiDB Cloud Lake. - [Golang](https://docs.pingcap.com/tidbcloudlake/connect-using-golang.md): This page describes how to connect to TiDB Cloud Lake using Golang. - [Java](https://docs.pingcap.com/tidbcloudlake/connect-using-java.md): This page describes how to connect to TiDB Cloud Lake using Java. - [Node.js](https://docs.pingcap.com/tidbcloudlake/connect-using-node-js.md): This page describes how to connect to TiDB Cloud Lake using Node.js. - [Python](https://docs.pingcap.com/tidbcloudlake/connect-using-python.md): This page describes how to connect to TiDB Cloud Lake using Python. - [Rust](https://docs.pingcap.com/tidbcloudlake/connect-using-rust.md): This page describes how to connect to TiDB Cloud Lake using Rust. - AI Tools - [External AI Functions](https://docs.pingcap.com/tidbcloudlake/external-ai-functions.md): Build powerful AI/ML capabilities by connecting TiDB Cloud Lake with your own infrastructure. External functions let you deploy custom models, leverage GPU acceleration, and integrate with any ML framework while keeping your data secure. - [MCP Client Integration](https://docs.pingcap.com/tidbcloudlake/mcp-client-integration.md): Learn how to connect MCP-compatible AI tools to TiDB Cloud Lake and use session sandbox protection for safe data exploration. - [MCP Server](https://docs.pingcap.com/tidbcloudlake/mcp-server.md): Learn how to install, run, and configure the TiDB Cloud Lake MCP server, including transports, safety controls, and available tools. - Visualization - [Tableau](https://docs.pingcap.com/tidbcloudlake/tableau.md): Tableau is a visual analytics platform transforming the way we use data to solve problems. You can connect Tableau to TiDB Cloud Lake through Tableau's Other Databases (JDBC) interface by using the lake-jdbc driver. - [Deepnote](https://docs.pingcap.com/tidbcloudlake/deepnote.md): Deepnote allows you to easily work on your data science projects, together in real-time and in one place with your friends and colleagues; helping you turn your ideas and analyses into products faster. Deepnote is built for the browser so you can use it across any platform (Windows, Mac, Linux or Chromebook). No downloads required, with updates shipped to you daily. All changes are instantly saved. - [Jupyter Notebook](https://docs.pingcap.com/tidbcloudlake/jupyter-notebook.md): Learn how to connect Jupyter Notebook to TiDB Cloud Lake with SQLAlchemy, run queries, and visualize query results with pandas. - [Superset](https://docs.pingcap.com/tidbcloudlake/superset.md): Learn how to install the TiDB Cloud Lake SQLAlchemy dialect in Apache Superset and connect Superset to a TiDB Cloud Lake warehouse. - Network Access - [Connect with AWS PrivateLink](https://docs.pingcap.com/tidbcloudlake/connect-with-aws-privatelink.md): PrivateLink-style private endpoints offered by major clouds (AWS PrivateLink, Azure Private Link, Google Private Service Connect, etc.) let you reach TiDB Cloud Lake through private IP addresses inside your own network boundary, so no traffic has to traverse the public internet. That keeps your datasets, credentials, and admin actions on the provider's backbone and aligned with the network policies you already operate. - [Connect with Alibaba Cloud PrivateLink](https://docs.pingcap.com/tidbcloudlake/connect-with-alibaba-cloud-privatelink.md): Configure an Alibaba Cloud private endpoint, enable its custom domain name, and verify private connectivity to TiDB Cloud Lake. - Manage Resources - [Dashboards](https://docs.pingcap.com/tidbcloudlake/dashboards.md): Dashboards are employed to present query results through a variety of chart types, including scorecards, pie charts, bar charts, and line charts. These charts are generated from the query results. You have the option to create a chart based on the query result after executing a query in a worksheet. Refreshing a dashboard allows you to re-execute the queries corresponding to the charts, thereby updating the charts with the latest results. - [Task Flow](https://docs.pingcap.com/tidbcloudlake/task-flow.md): Task Flow is TiDB Cloud Lake's built-in workflow orchestration feature. It lets you define, schedule, and monitor SQL-based data pipelines as directed acyclic graphs (DAGs). - [Warehouses](https://docs.pingcap.com/tidbcloudlake/warehouse.md): The warehouse is an essential component of TiDB Cloud Lake. A warehouse represents a set of compute resources including CPU, memory, and local caches. You must run a warehouse to perform SQL tasks. - [Worksheets](https://docs.pingcap.com/tidbcloudlake/worksheet.md): Worksheets in TiDB Cloud Lake are used to organize, run, and save SQL statements. They can also be shared with others in your organization. - Data Integration - [Overview](https://docs.pingcap.com/tidbcloudlake/data-integration-overview.md): The Data Integration feature in TiDB Cloud Lake provides a visual, no-code interface for importing or synchronizing data from external systems into TiDB Cloud Lake. - Data Sources - [Overview](https://docs.pingcap.com/tidbcloudlake/data-sources.md): A data source in TiDB Cloud Lake represents a connection to an external system. It stores the credentials and connection details required to access external systems and can be reused across multiple integration tasks or notification scenarios. - [Amazon S3 - Credentials](https://docs.pingcap.com/tidbcloudlake/aws-credentials.md): This page describes how to create an "Amazon S3 - Credentials" data source. This data source stores the credentials required to access Amazon S3 and can be reused across multiple S3 integration tasks. - [Amazon SQS (S3) - IAM Role](https://docs.pingcap.com/tidbcloudlake/amazon-sqs-s3-iam-role.md): Learn how to create an "Amazon SQS (S3) - IAM Role" data source in TiDB Cloud Lake. ![PREVIEW](https://docs-download.pingcap.com/media/images/docs/tidb-cloud/blank_transparent_placeholder.png) - [MySQL - Credentials](https://docs.pingcap.com/tidbcloudlake/mysql-credentials.md): This page describes how to create a "MySQL - Credentials" data source. This data source stores the connection information required to access MySQL and can be reused across multiple MySQL integration tasks. - [PostgreSQL - Credentials](https://docs.pingcap.com/tidbcloudlake/postgresql-credentials.md): This page describes how to create a "PostgreSQL - Credentials" data source. This data source stores the connection information required to access PostgreSQL and can be reused across multiple PostgreSQL integration tasks. - [FeiShuBot](https://docs.pingcap.com/tidbcloudlake/feishubot.md): This page describes how to create a "FeiShuBot" data source. This data source stores a FeiShu bot webhook and message template for task failure notifications and similar scenarios. - [Kafka - Credentials](https://docs.pingcap.com/tidbcloudlake/kafka-credentials.md): Create a "Kafka - Credentials" data source to store Kafka connection information for reuse in Kafka Consumer integration tasks. ![PREVIEW](https://docs-download.pingcap.com/media/images/docs/tidb-cloud/blank_transparent_placeholder.png) - Integration Tasks - [Overview](https://docs.pingcap.com/tidbcloudlake/integration-tasks.md): This page provides an overview of integration tasks in TiDB Cloud Lake. Integration tasks define how data flows from external sources into TiDB Cloud Lake, including source settings, target tables, and runtime parameters. - [Task Management](https://docs.pingcap.com/tidbcloudlake/task-management.md): This page describes common operations for integration tasks, including the task creation flow, start and stop behavior, task states, and run history. For source-specific configuration, see the detailed task guides. - [Amazon S3 Integration Task](https://docs.pingcap.com/tidbcloudlake/integrate-with-amazon-s3.md): The Amazon S3 data integration enables you to import files from S3 buckets into TiDB Cloud Lake. It supports CSV, Parquet, and NDJSON file formats, with options for one-time imports or continuous ingestion that automatically polls for new files. - [Amazon SQS (S3) Integration Task](https://docs.pingcap.com/tidbcloudlake/integrate-with-amazon-sqs-s3.md): Learn how to create an Amazon SQS (S3) integration task that consumes S3 object creation events from an SQS queue and writes the corresponding object data into TiDB Cloud Lake. ![PREVIEW](https://docs-download.pingcap.com/media/images/docs/tidb-cloud/blank_transparent_placeholder.png) - [MySQL Integration Task](https://docs.pingcap.com/tidbcloudlake/integrate-with-mysql.md): The MySQL data integration enables you to sync data from MySQL databases into TiDB Cloud Lake in real-time, with support for full snapshot loads, continuous Change Data Capture (CDC), or a combination of both. - [PostgreSQL Integration Task](https://docs.pingcap.com/tidbcloudlake/integrate-with-postgresql.md): This page describes how to create a PostgreSQL integration task that synchronizes data from a PostgreSQL database into TiDB Cloud Lake. - [Kafka Consumer Integration Task](https://docs.pingcap.com/tidbcloudlake/integrate-with-kafka.md): Create a Kafka Consumer task to continuously consume messages from Kafka topics and save the message content to internal object storage (tenant Stage). ![PREVIEW](https://docs-download.pingcap.com/media/images/docs/tidb-cloud/blank_transparent_placeholder.png) - Load Data - Work with Stages - [Stage Overview](https://docs.pingcap.com/tidbcloudlake/stage-overview.md): A stage is a virtual location where data files reside. Files in a stage can be queried directly or loaded into a table. Alternatively, you can unload data from a table into a stage as a file. - [Upload to Stage](https://docs.pingcap.com/tidbcloudlake/upload-to-stage.md): TiDB Cloud Lake recommends two file upload methods for stages PRESIGN and PUT/GET commands. These methods enable direct data transfer between the client and your storage, eliminating intermediaries and resulting in cost savings by reducing traffic between TiDB Cloud Lake and your storage. - Load from Files - [Overview](https://docs.pingcap.com/tidbcloudlake/load-from-files.md): TiDB Cloud Lake offers simple, powerful commands to load data files into tables. Most operations require just a single command. - [Load from Stage](https://docs.pingcap.com/tidbcloudlake/load-from-stage.md): TiDB Cloud Lake enables you to easily import data from files uploaded to either the user stage or an internal/external stage. To do so, you can first upload the files to a stage using LakeSQL, and then employ the COPY INTO command to load the data from the staged file. Please note that the files must be in a format supported by TiDB Cloud Lake, otherwise the data cannot be imported. For more information on the file formats supported by TiDB Cloud Lake, see Input & Output File Formats. - [Load from Bucket](https://docs.pingcap.com/tidbcloudlake/load-from-bucket.md): When data files are stored in an object storage bucket, such as Amazon S3, it is possible to load them directly into TiDB Cloud Lake using the COPY INTO command. Please note that the files must be in a format supported by TiDB Cloud Lake, otherwise the data cannot be imported. For more information on the file formats supported by TiDB Cloud Lake, see Input & Output File Formats. - [Load from Local File](https://docs.pingcap.com/tidbcloudlake/load-from-local-file.md): Uploading your local data files to a stage or bucket before loading them into TiDB Cloud Lake can be unnecessary. Instead, you can use LakeSQL, the TiDB Cloud Lake native CLI tool, to directly import the data. This simplifies the workflow and can save you storage fees. - [Load from Remote File](https://docs.pingcap.com/tidbcloudlake/load-from-remote-file.md): To load data from remote files into TiDB Cloud Lake, the COPY INTO command can be used. This command allows you to copy data from a variety of sources, including remote files, into TiDB Cloud Lake with ease. With COPY INTO, you can specify the source file location, file format, and other relevant parameters to tailor the import process to your needs. Please note that the files must be in a format supported by TiDB Cloud Lake, otherwise the data cannot be imported. For more information on the file formats supported by TiDB Cloud Lake, see Input & Output File Formats. - Load with Platforms - [Load with dbt](https://docs.pingcap.com/tidbcloudlake/load-with-dbt.md): dbt is a transformation workflow that helps you get more work done while producing higher quality results. You can use dbt to modularize and centralize your analytics code, while also providing your data team with guardrails typically found in software engineering workflows. Collaborate on data models, version them, and test and document your queries before safely deploying them to production, with monitoring and visibility. - Load Semi-structured Data - [Overview](https://docs.pingcap.com/tidbcloudlake/load-semi-structured-data.md): Semi-structured data contains tags or markers to separate semantic elements while not conforming to rigid database structures. TiDB Cloud Lake efficiently loads these formats using the COPY INTO command, with optional on-the-fly data transformation. - [Load Parquet](https://docs.pingcap.com/tidbcloudlake/load-parquet.md): Parquet is a columnar storage format commonly used in data analytics. It is designed to support complex data structures, and it is efficient for processing large datasets. - [Load CSV](https://docs.pingcap.com/tidbcloudlake/load-csv.md): CSV (Comma Separated Values) is a simple file format used to store tabular data, such as a spreadsheet or database. CSV files are plain text files that contain data in a tabular format, where each row is represented on a new line, and columns are separated by a delimiter. - [Load TSV](https://docs.pingcap.com/tidbcloudlake/load-tsv.md): TSV (Tab Separated Values) is a simple file format used to store tabular data, such as a spreadsheet or database. The TSV file format is very similar to CSV, the records are separated by newlines, and each field is separated by a tab. The following example shows a TSV file with two records. - [Load NDJSON](https://docs.pingcap.com/tidbcloudlake/load-ndjson.md): NDJSON is built on top of JSON, and it is a strict subset of JSON. Each line must contain a separate, self-contained valid JSON object. - [Load ORC](https://docs.pingcap.com/tidbcloudlake/load-orc.md): ORC (Optimized Row Columnar) is a columnar storage format commonly used in data analytics. - [Load Avro](https://docs.pingcap.com/tidbcloudlake/load-avro.md): Apache Avro™ is the leading serialization format for record data, and first choice for streaming data pipelines. - Query & Transform - [Overview](https://docs.pingcap.com/tidbcloudlake/query-stage.md): TiDB Cloud Lake enables direct querying of staged files without loading data into tables first. Query files from any stage type (user, internal, external) or directly from object storage and HTTPS URLs. Ideal for data inspection, validation, and transformation before or after loading. - [Query Parquet Files](https://docs.pingcap.com/tidbcloudlake/query-parquet-files-in-stage.md): Create an external stage with your own S3 bucket and credentials where your Parquet files are stored. - [Query CSV Files](https://docs.pingcap.com/tidbcloudlake/query-csv-files-in-stage.md): Create an external stage with your own S3 bucket and credentials where your CSV files are stored. - [Query TSV Files](https://docs.pingcap.com/tidbcloudlake/query-tsv-files-in-stage.md): Create an external stage with your own S3 bucket and credentials where your TSV files are stored. - [Query NDJSON Files](https://docs.pingcap.com/tidbcloudlake/query-ndjson-files-in-stage.md): In TiDB Cloud Lake, you can directly query NDJSON files stored in stages without first loading the data into tables. This approach is particularly useful for data exploration, ETL processing, and ad-hoc analysis scenarios. - [Query Avro Files](https://docs.pingcap.com/tidbcloudlake/query-avro-files-in-stage.md): TiDB Cloud Lake provides comprehensive support for querying Avro files directly from stages. This allows for flexible data exploration and transformation without needing to load the data into a table first. - [Query Staged ORC Files](https://docs.pingcap.com/tidbcloudlake/query-staged-orc-files-in-stage.md): In this tutorial, we will walk you through the process of downloading the Iris dataset in ORC format, uploading it to an Amazon S3 bucket, creating an external stage, and querying the data directly from the ORC file. - [Transform Data on Load](https://docs.pingcap.com/tidbcloudlake/transform-data-on-load.md): TiDB Cloud Lake's COPY INTO command allows data transformation during the loading process. This streamlines your ETL pipeline by incorporating basic transformations, eliminating the need for temporary tables. - [Schema Evolution](https://docs.pingcap.com/tidbcloudlake/schema-evolution.md): Automatically evolve table schemas when loading data with COPY INTO. - Continuous Data Pipelines - [Overview](https://docs.pingcap.com/tidbcloudlake/continuous-data-pipelines.md): Build end-to-end change data capture (CDC) flows in TiDB Cloud Lake with two primitives. - [Track and Transform Data via Streams](https://docs.pingcap.com/tidbcloudlake/track-and-transform-data-via-streams.md): A stream in TiDB Cloud Lake is an always-on change table every committed INSERT, UPDATE, or DELETE is captured until you consume it. This page stays lean—first a quick overview, then one lab with real outputs so you can see streams in action. - [Automate Data Loading with Tasks](https://docs.pingcap.com/tidbcloudlake/automate-data-loading-with-tasks.md): Tasks wrap SQL so TiDB Cloud Lake can run it for you on a schedule or when a condition is met. Keep the following knobs in mind when you define one with CREATE TASK. - Unload Data - [Overview](https://docs.pingcap.com/tidbcloudlake/unload-data.md): Learn how to unload data from TiDB Cloud Lake to various file formats and storage destinations using the `COPY INTO` command. - [Unload Parquet File](https://docs.pingcap.com/tidbcloudlake/unload-parquet-file.md): Learn about how to unload parquet files. - [Unload CSV File](https://docs.pingcap.com/tidbcloudlake/unload-csv-file.md): Learn about how to unload CSV files. - [Unload TSV File](https://docs.pingcap.com/tidbcloudlake/unload-tsv-file.md): Learn about how to unload TSV files. - [Unload NDJSON File](https://docs.pingcap.com/tidbcloudlake/unload-ndjson-file.md): Learn about how to unload NDJSON files. - [Unload Lance Dataset](https://docs.pingcap.com/tidbcloudlake/unload-lance-dataset.md): Learn about how to unload Lance datasets. - Multimodal Data Analytics - [Overview](https://docs.pingcap.com/tidbcloudlake/multimodal-data-analytics.md): CityDrive Intelligence records video of every drive. Background processing tools split the video stream into keyframe images, extracting rich multimodal information from each image and storing it by video_id. These signals include relational metadata, JSON manifests, behavior tags, vector embeddings, and GPS traces. - [SQL Analytics](https://docs.pingcap.com/tidbcloudlake/sql-analytics.md): Scenario CityDrive stages all dash-cam records into shared relational tables. This relational data (e.g., video metadata, event tags) is extracted by background processing pipelines from keyframes of the raw dash-cam video. - [JSON & Search](https://docs.pingcap.com/tidbcloudlake/json-search.md): Scenario CityDrive attaches a metadata JSON payload to every extracted frame. This JSON data is extracted from video keyframes by background tools, containing rich unstructured information like scene recognition and object detection. We need to filter this JSON in TiDB Cloud Lake with Elasticsearch-style syntax without replicating it to an external system. JSON without copying it out of TiDB Cloud Lake. - [Vector Search](https://docs.pingcap.com/tidbcloudlake/vector-search-guide.md): Scenario CityDrive keeps embeddings for every frame directly in TiDB Cloud Lake. These vector embeddings are the result of AI models inferencing on video keyframes to capture visual semantic features. Semantic similarity search ("find frames that look like this") can run alongside traditional SQL analytics—no separate vector service required. - [Geo Analytics](https://docs.pingcap.com/tidbcloudlake/geo-analytics.md): CityDrive records precise GPS positioning and distance-to-signal for every flagged frame. This geospatial data originates from the dash-cam's GPS module and is precisely aligned with the timestamps of video keyframes. Ops teams can answer "where did this happen?" purely in SQL. - [Lakehouse ETL](https://docs.pingcap.com/tidbcloudlake/lakehouse-etl.md): Scenario CityDrive's data engineering team exports every batch of dash-cam data as Parquet (videos, frame events, metadata JSON, embeddings, GPS traces, traffic light distances). These Parquet files aggregate all multimodal signals extracted from the raw video streams, forming the foundation of the warehouse. They want to update TiDB Cloud Lake's shared tables via a single COPY pipeline. to refresh the shared tables in TiDB Cloud Lake. - Performance Tuning - [Overview](https://docs.pingcap.com/tidbcloudlake/performance-optimization.md): TiDB Cloud Lake primarily accelerates query performance through various indexing technologies, including data clustering, result caching, and specialized indexes, helping you significantly improve query response times. - [Cluster Key](https://docs.pingcap.com/tidbcloudlake/cluster-key-performance.md): Cluster keys provide automatic data organization to dramatically improve query performance on large tables. TiDB Cloud Lake seamlessly and continually manages all clustering operations in the background - you simply define the cluster key and TiDB Cloud Lake handles the rest. - [Virtual Column](https://docs.pingcap.com/tidbcloudlake/virtual-column.md): Virtual columns automatically accelerate queries on semi-structured data stored in VARIANT columns. This feature provides zero-configuration performance optimization for JSON data access. - [Aggregating Index](https://docs.pingcap.com/tidbcloudlake/aggregating-index.md): Aggregating indexes dramatically accelerate analytical queries by precomputing and storing aggregation results, eliminating the need to scan entire tables for common analytics operations. - [Full-Text Index](https://docs.pingcap.com/tidbcloudlake/full-text-index.md): Full-text indexes (inverted indexes) automatically enable lightning-fast text searches across large document collections by mapping terms to documents, eliminating the need for slow table scans. - [Ngram Index](https://docs.pingcap.com/tidbcloudlake/ngram-index.md): Ngram indexes accelerate pattern matching queries using the LIKE operator with wildcards (%), enabling fast substring searches without full table scans. - [Query Result Cache](https://docs.pingcap.com/tidbcloudlake/query-result-cache.md): TiDB Cloud Lake caches and persists the query results for every executed query when enabled. This can be used to great effect to dramatically reduce the time it takes to get an answer. - Security & Reliability - [Overview](https://docs.pingcap.com/tidbcloudlake/security-reliability.md): TiDB Cloud Lake offers enterprise-grade security and reliability features that safeguard your data throughout its lifecycle. From controlling who can access your data to protecting against network threats and recovering from operational errors, TiDB Cloud Lake's multi-layered security approach helps you maintain data integrity, compliance, and business continuity. - Access Control - [Overview](https://docs.pingcap.com/tidbcloudlake/access-control.md): Learn about access control in TiDB Cloud Lake, which uses both Role-Based Access Control (RBAC) and Discretionary Access Control (DAC) to manage permissions for data objects like databases, tables, and views. - [Privileges](https://docs.pingcap.com/tidbcloudlake/privileges.md): A privilege is a permission to perform an action. Users must have specific privileges to execute particular actions within TiDB Cloud Lake. For example, when querying a table, a user needs SELECT privileges to the table. Similarly, to read a dataset within a stage, the user must possess READ privileges. - [Roles](https://docs.pingcap.com/tidbcloudlake/roles.md): Roles in TiDB Cloud Lake play a pivotal role in simplifying the management of permissions. When multiple users require the same set of privileges, granting privileges individually can be cumbersome. Roles provide a solution by allowing the assignment of a set of privileges to a role, which can then be easily assigned to multiple users. - [Ownership](https://docs.pingcap.com/tidbcloudlake/ownership.md): Ownership is a specialized privilege that signifies the exclusive rights and responsibilities a role holds over a specific data object (currently including a database, table, UDF, and stage) within TiDB Cloud Lake. - Data Protection Policies - [Overview](https://docs.pingcap.com/tidbcloudlake/data-protection-policies.md): Learn the masking policy and row access policy that safeguard sensitive information without altering stored values. - [Masking Policy](https://docs.pingcap.com/tidbcloudlake/masking-policy.md): Masking policies protect sensitive data by dynamically transforming column values during query execution. They enable role-based access to confidential information—authorized users see actual data, while others see masked values. - [Row Access Policy](https://docs.pingcap.com/tidbcloudlake/row-access-policy.md): Row access policies protect data by filtering table rows at query time. They let you define centralized row-level predicates once, attach them to tables, and ensure users only see rows that satisfy the policy. - [Audit Trail](https://docs.pingcap.com/tidbcloudlake/audit-trail.md): TiDB Cloud Lake system history tables automatically capture detailed records of database activities, providing a complete audit trail for compliance and security monitoring. - [Network Policy](https://docs.pingcap.com/tidbcloudlake/network-policy.md): Network policies control who can log in to TiDB Cloud Lake based on the client IP. Even if the credentials are correct, a connection request is rejected when its IP does not satisfy the policy, giving you an extra security layer beyond username and password. - [Password Policy](https://docs.pingcap.com/tidbcloudlake/password-policy.md): Password policies define how strong a TiDB Cloud Lake password must be (length, characters, history, retry limits, and more) and how often it can change. They add predictable guardrails around every CREATE USER and password change. For the full list of attributes, see Password Policy Attributes. - [Authenticate with AWS IAM Role](https://docs.pingcap.com/tidbcloudlake/authenticate-with-aws-iam-role.md): Cloud-native identity delegation (AWS IAM Role, Azure Managed Identity, Google Service Account federation, etc.) lets TiDB Cloud Lake obtain short-lived credentials to your object storage without ever handling raw access keys. That keeps data plane access inside your cloud provider's control plane while you retain ownership of every permission. - [Compliance & Security](https://docs.pingcap.com/tidbcloudlake/compliance-security.md): TiDB Cloud Lake is built with security at its core, providing comprehensive protection for your data through multiple security layers, encryption standards, and compliance certifications. - [Fail-Safe](https://docs.pingcap.com/tidbcloudlake/fail-safe.md): Fail-Safe refers to mechanisms aimed at recovering lost or accidentally deleted data from object storage. - [Recovery from Operational Errors](https://docs.pingcap.com/tidbcloudlake/recovery-from-operational-errors.md): This guide provides step-by-step instructions for recovering from common operational errors in TiDB Cloud Lake. - Management - [AI-Powered Features](https://docs.pingcap.com/tidbcloudlake/ai-powered-features.md): With the inclusion of AI-powered features, TiDB Cloud Lake allows you to engage in natural language conversations to receive help, assistance, and solutions. - [Manage Costs](https://docs.pingcap.com/tidbcloudlake/manage-costs.md): You can view the current month's consumption and billing history for your organization under Manage > Billing. - [Monitor Usage](https://docs.pingcap.com/tidbcloudlake/monitor-usage.md): TiDB Cloud Lake provides monitoring functionality to help you gain a comprehensive understanding of your and your organization members' usage on the platform. To access the Monitor page, click Monitor in the sidebar menu on the homepage. The page includes the following tabs. - [Data Lifecycle](https://docs.pingcap.com/tidbcloudlake/data-lifecycle.md): TiDB Cloud Lake supports familiar Data Definition Language (DDL) and Data Manipulation Language (DML) commands, making it easy for you to manage your database. Whether you're organizing, storing, querying, modifying, or deleting data, TiDB Cloud Lake follows the same industry standards you're accustomed to. - [Data Lineage](https://docs.pingcap.com/tidbcloudlake/data-lineage.md): Learn how to enable and explore data lineage in TiDB Cloud Lake. - [Data Protection](https://docs.pingcap.com/tidbcloudlake/data-protection.md): TiDB Cloud Lake's Continuous Data Protection (CDP) offers easy-to-use features to keep your data safe from mistakes, harmful actions, and software problems. It ensures your data can always be recovered, even if it's changed, lost, or damaged by accident or on purpose. - [Data Purge and Recycle](https://docs.pingcap.com/tidbcloudlake/data-purge-and-recycle.md): In TiDB Cloud Lake, data is not immediately deleted when you run DROP, TRUNCATE, or DELETE commands. This enables TiDB Cloud Lake's time travel feature, allowing you to access previous states of your data. However, this approach means that storage space is not automatically freed up after these operations. - [Pricing & Billing](https://docs.pingcap.com/tidbcloudlake/pricing-billing.md): Understand the pricing model and billing details for TiDB Cloud Lake. - [Troubleshooting](https://docs.pingcap.com/tidbcloudlake/troubleshooting.md): This page describes how to troubleshoot common issues in TiDB Cloud Lake. ## Tutorials - Ingest & Stream Data - [Ingest JSON Logs with Vector (Cloud)](https://docs.pingcap.com/tidbcloudlake/ingest-json-logs-with-vector-cloud.md): In this tutorial, we'll simulate generating logs locally, collect them using Vector, store them in S3, and automate their ingestion into TiDB Cloud Lake using scheduled tasks. - Migrate Data - [Migrate from Snowflake](https://docs.pingcap.com/tidbcloudlake/migrate-from-snowflake.md): Migrates data from Snowflake to TiDB Cloud Lake by exporting data to Amazon S3 and loading it into TiDB Cloud Lake tables. ## Reference - SQL Reference - [Overview](https://docs.pingcap.com/tidbcloudlake/sql-statements-overview.md): These topics provide reference information for various SQL commands in TiDB Cloud Lake. - SQL General - Data Types - [Overview](https://docs.pingcap.com/tidbcloudlake/data-types.md): TiDB Cloud Lake stores data in strongly typed columns. This page summarizes the supported data types, how automatic/explicit conversions work, and what happens with NULL or default values. - [Array](https://docs.pingcap.com/tidbcloudlake/array.md): Array of defined data type. - [Binary](https://docs.pingcap.com/tidbcloudlake/binary.md): Variable-length sequences of raw bytes. - [Bitmap](https://docs.pingcap.com/tidbcloudlake/bitmap.md): BITMAP stores membership information for unsigned 64-bit integers and supports fast set operations (count, union, intersection, etc.). SELECT statements show a binary blob, so use Bitmap Functions to interpret the values. - [Boolean](https://docs.pingcap.com/tidbcloudlake/boolean.md): Basic logical data type. - [Date & Time](https://docs.pingcap.com/tidbcloudlake/date-time.md): TiDB Cloud Lake's Date and Time data type supports standardization and compatibility with various SQL standards, making it easier for users migrating from other database systems. - [Decimal](https://docs.pingcap.com/tidbcloudlake/decimal.md): Decimal types are high-precision numeric values to be stored and manipulated. - [Geospatial](https://docs.pingcap.com/tidbcloudlake/geospatial.md): TiDB Cloud Lake stores spatial data through two data types. - [Interval](https://docs.pingcap.com/tidbcloudlake/interval.md): INTERVAL represents a duration that can be written in natural-language text ('1 year 2 months', '3 days ago') or as an integer number of microseconds. TiDB Cloud Lake supports units from millennia down to microseconds and allows arithmetic on intervals, dates, and timestamps. - [Map](https://docs.pingcap.com/tidbcloudlake/map.md): MAP(K, V) stores key-value pairs internally as ARRAY(TUPLE(key, value)). Define the key type K up front (Boolean, numeric, decimal, string, date, or timestamp). Keys must be non-null and unique; values can be any type, including nested structures. Use map literals ({key value}) or the MAP(keys, values) function to build a map expression. - [Numeric](https://docs.pingcap.com/tidbcloudlake/numeric.md): Basic Numeric data type. - [String](https://docs.pingcap.com/tidbcloudlake/string.md): Basic String data type. - [Tuple](https://docs.pingcap.com/tidbcloudlake/tuple.md): Tuple is a collection of ordered, immutable types. - [Variant](https://docs.pingcap.com/tidbcloudlake/variant.md): A VARIANT can store a value of any other type, including NULL, BOOLEAN, NUMBER, STRING, ARRAY, and OBJECT, and the internal value can be any level of nested structure, which is very flexible to store various data. VARIANT can also be called JSON, for more information, please refer to JSON website. - [Vector](https://docs.pingcap.com/tidbcloudlake/vector.md): The VECTOR data type stores multi-dimensional arrays of 32-bit floating-point numbers, designed for machine learning, AI applications, and similarity search operations. Each vector has a fixed dimension (length) specified at creation time. - Information Schema - [Information_Schema Tables](https://docs.pingcap.com/tidbcloudlake/information-schema-tables-overview.md): This page describes Information_Schema Tables in TiDB Cloud Lake. - [information_schema.columns](https://docs.pingcap.com/tidbcloudlake/information-schema-columns-sql.md): Contains information about columns of tables. - [information_schema.keywords](https://docs.pingcap.com/tidbcloudlake/information-schema-keywords-sql.md): The information_schema.keywords system table is a view that provides all keywords in TiDB Cloud Lake. - [information_schema.schemata](https://docs.pingcap.com/tidbcloudlake/information-schema-schemata-sql.md): Provides metadata about all databases in the system. - [information_schema.tables](https://docs.pingcap.com/tidbcloudlake/information-schema-tables-sql.md): The information_schema.tables system table is a view that provides metadata about all tables across all databases, including their schema, type, engine, and creation details. It also includes storage metrics such as data length, index length, and row count, offering insights into table structure and usage. - [information_schema.views](https://docs.pingcap.com/tidbcloudlake/information-schema-views-sql.md): Provides metadata information for all views. - Table Engines - [Overview](https://docs.pingcap.com/tidbcloudlake/table-engines.md): TiDB Cloud Lake provides several table engines so that you can balance performance and interoperability needs without moving data. Each engine is optimized for a specific scenario—ranging from TiDB Cloud Lake’s native Fuse storage to external data lake formats. - [Fuse Engine Tables](https://docs.pingcap.com/tidbcloudlake/fuse-engine-tables.md): TiDB Cloud Lake uses the Fuse Engine as its default storage engine, providing a Git-like data management system with. - [Apache Iceberg™ Tables](https://docs.pingcap.com/tidbcloudlake/apache-icebergtm-tables.md): Learn how to connect TiDB Cloud Lake to Apache Iceberg catalogs and query or write Iceberg tables. - [Apache Hive Tables](https://docs.pingcap.com/tidbcloudlake/apache-hive-tables.md): TiDB Cloud Lake can query data that is cataloged by Apache Hive without copying it. Register the Hive Metastore as a TiDB Cloud Lake catalog, point to the object storage that holds the table data, and then query the tables as if they were native TiDB Cloud Lake objects. - [Delta Lake Engine](https://docs.pingcap.com/tidbcloudlake/delta-lake-engine.md): TiDB Cloud Lake's Delta Lake engine allows you to seamlessly query and analyze data in Delta Lake tables stored in your object storage. When you create a table with the Delta Lake engine in TiDB Cloud Lake, you specify a location where the data files of a Delta Lake table are stored. This setup allows you to gain direct access to the table and perform queries seamlessly from within TiDB Cloud Lake. - System Tables - [Overview](https://docs.pingcap.com/tidbcloudlake/system-tables.md): TiDB Cloud Lake provides a set of system tables that contain metadata about your TiDB Cloud Lake deployment, databases, tables, queries, and system performance. These tables are read-only and are automatically updated by the system. - [system.build_options](https://docs.pingcap.com/tidbcloudlake/system-build-options.md): This system table describes the build options of the current TiDB Cloud Lake release. - [system.caches](https://docs.pingcap.com/tidbcloudlake/system-caches.md): An overview of various caches being managed in TiDB Cloud Lake. - [system.clusters](https://docs.pingcap.com/tidbcloudlake/system-clusters.md): Contains information about cluster nodes. - [system.columns](https://docs.pingcap.com/tidbcloudlake/system-columns.md): Contains information about columns of tables. - [system.contributors](https://docs.pingcap.com/tidbcloudlake/system-contributors.md): Contains information about contributors. - [system.copy_history](https://docs.pingcap.com/tidbcloudlake/system-copy-history.md): Contains information about copy history. - [system.credits](https://docs.pingcap.com/tidbcloudlake/system-credits.md): Contains information about credits. - [system.databases](https://docs.pingcap.com/tidbcloudlake/system-databases.md): Provides metadata about all databases in the system, including their catalogs, names, unique IDs, owners, and drop timestamps. - [system.databases_with_history](https://docs.pingcap.com/tidbcloudlake/system-databases-with-history.md): Records all databases, including active and dropped ones. It shows each database's catalog, name, unique ID, owner (if specified), and the deletion timestamp (NULL if still active). - [system.functions](https://docs.pingcap.com/tidbcloudlake/system-functions.md): Contains information about the supported built-in scalar and aggregate. - [system.indexes](https://docs.pingcap.com/tidbcloudlake/system-indexes.md): Contains information about the created indexes. - [system.locks](https://docs.pingcap.com/tidbcloudlake/system-locks.md): Contains information about the locks in the system. - [system.metrics](https://docs.pingcap.com/tidbcloudlake/system-metrics.md): Contains information about metric events. - [system.numbers](https://docs.pingcap.com/tidbcloudlake/system-numbers.md): This table contains a single UInt64 column named number that contains almost all the natural numbers starting from zero. - [system.query_cache](https://docs.pingcap.com/tidbcloudlake/system-query-cache.md): Stores information about cached query results, including details such as the query ID, result size, and the location of the cached data. - [system.query_log](https://docs.pingcap.com/tidbcloudlake/system-query-log.md): A read-only in-memory table stores all the query logs. For the specific fields available in the query logs, refer to the Examples section. - [system.settings](https://docs.pingcap.com/tidbcloudlake/system-settings.md): Stores the system settings of the current session. - [system.streams](https://docs.pingcap.com/tidbcloudlake/system-streams.md): Provides information about streams in the system. Each row in this table corresponds to a stream, and the columns contain details such as the stream's mode, comment (if any), associated table name, table ID, table version, snapshot location, invalid reason (if applicable), and owner. - [system.table_functions](https://docs.pingcap.com/tidbcloudlake/system-table-functions.md): Get all table functions' names. - [system.tables](https://docs.pingcap.com/tidbcloudlake/system-tables-sql.md): Provides metadata information for all tables. It includes details such as table properties, creation time, number of rows, data size, and more. - [system.tables_with_history](https://docs.pingcap.com/tidbcloudlake/system-tables-with-history.md): Provides metadata information for all tables, including historical tables. It includes details such as table properties, creation time, number of rows, data size, and more. - [system.temp_files](https://docs.pingcap.com/tidbcloudlake/system-temp-files.md): Contains information about temporary files created by TiDB Cloud Lake, such as spill files. To remove the temporary files, use the VACUUM TEMPORARY FILES command. - [system.temporary_tables](https://docs.pingcap.com/tidbcloudlake/system-temporary-tables.md): Provides information about all existing temporary tables in the current session. - [system.user_functions](https://docs.pingcap.com/tidbcloudlake/system-user-functions.md): Contains information about user-defined functions and external functions in the system. - [system.views](https://docs.pingcap.com/tidbcloudlake/system-views.md): Provides metadata information for all views. - [system.virtual_columns](https://docs.pingcap.com/tidbcloudlake/system-virtual-columns.md): Contains information about the created virtual columns in the system. - System History Tables - [system_history.access_history](https://docs.pingcap.com/tidbcloudlake/system-history-access-history.md): The fields base_objects_accessed, objects_modified, and object_modified_by_ddl are all arrays of JSON objects. Each object may include the following fields. - [system_history.log_history](https://docs.pingcap.com/tidbcloudlake/system-history-log-history.md): Note This table contains raw log data that feeds into other specialized history tables. Other tables provide structured, query-specific views of this data. - [system_history.login_history](https://docs.pingcap.com/tidbcloudlake/system-history-login-history.md): Login successful example sql SELECT * FROM system_history.login_history LIMIT 1;. - [system_history.profile_history](https://docs.pingcap.com/tidbcloudlake/system-history-profile-history.md): The profiles field can be used to extract specific information. For example, to get the OutputRows value for every physical plan, the following query can be used sql SELECT jq('[.[] | {id, output_rows.statistics[4]}]', profiles) AS result FROM system_history.profile_history LIMIT 1;. - [system_history.query_history](https://docs.pingcap.com/tidbcloudlake/system-history-query-history.md): Query the history for a specific query using its query_id. - [SQL Identifiers](https://docs.pingcap.com/tidbcloudlake/sql-identifiers.md): SQL identifiers are names used for different elements within TiDB Cloud Lake, such as tables, views, and databases. - [Input & Output File Formats](https://docs.pingcap.com/tidbcloudlake/input-output-file-formats.md): TiDB Cloud Lake accepts a variety of file formats both as a source and as a target for data loading or unloading. This page explains the supported file formats and their available options. - [Connection Parameters](https://docs.pingcap.com/tidbcloudlake/connection-parameters.md): Connection parameters are key-value pairs you supply when creating reusable connections with CREATE CONNECTION. After a connection is created, reference it from stages, COPY commands, and other SQL features by using CONNECTION = (CONNECTION_NAME = ''). For full syntax and usage, see CREATE CONNECTION. - [SQL Dialects & Conformance](https://docs.pingcap.com/tidbcloudlake/sql-dialects-conformance.md): This page provides details on the SQL dialects supported by TiDB Cloud Lake, along with its conformity to the SQL standard, particularly focusing on SQL2011 features and their support status within TiDB Cloud Lake. - SQL Statements - [Overview](https://docs.pingcap.com/tidbcloudlake/sql-statements-reference.md): This page moved to SQL statements overview. - DDL Commands - [DDL Overview](https://docs.pingcap.com/tidbcloudlake/ddl.md): These topics provide reference information for the DDL (Data Definition Language) commands in TiDB Cloud Lake. - Database - [Overview](https://docs.pingcap.com/tidbcloudlake/ddl-database-overview.md): This page provides a comprehensive overview of database operations in TiDB Cloud Lake, organized by functionality for easy reference. - [CREATE DATABASE](https://docs.pingcap.com/tidbcloudlake/create-database.md): Create a database. - [SHOW CREATE DATABASE](https://docs.pingcap.com/tidbcloudlake/show-create-database.md): Shows the CREATE DATABASE statement that creates the named database. - [USE DATABASE](https://docs.pingcap.com/tidbcloudlake/use-database.md): Selects a database for the current session. This statement allows you to specify and switch to a different database. Once you set the current database using this command, it remains the same until the end of the session unless you choose to change it. - [ALTER DATABASE](https://docs.pingcap.com/tidbcloudlake/alter-database.md): Changes the name of a database, or sets default storage options for a database. - [SHOW DATABASES](https://docs.pingcap.com/tidbcloudlake/show-databases.md): Shows the list of databases that exist on the instance. - [DROP DATABASE](https://docs.pingcap.com/tidbcloudlake/drop-database.md): Drops a database. - [SHOW DROP DATABASES](https://docs.pingcap.com/tidbcloudlake/show-drop-databases.md): Lists all databases along with their deletion timestamps if they have been dropped, allowing users to review deleted databases and their details. - [UNDROP DATABASE](https://docs.pingcap.com/tidbcloudlake/undrop-database.md): Restores the most recent version of a dropped database. This leverages the TiDB Cloud Lake Time Travel feature; a dropped object can be restored only within a retention period (defaults to 24 hours). - Table - [Overview](https://docs.pingcap.com/tidbcloudlake/ddl-table-overview.md): This page provides a comprehensive overview of table operations in TiDB Cloud Lake, organized by functionality for easy reference. - [CREATE TABLE](https://docs.pingcap.com/tidbcloudlake/create-table.md): Creating tables is one of the most complicated operations for many databases because you might need to. - [CREATE EXTERNAL TABLE](https://docs.pingcap.com/tidbcloudlake/create-external-table.md): The CREATE TABLE... CONNECTION = (...) statement creates a table and specifies an S3-compatible storage bucket for data storage instead of using the default local storage. - [CREATE TEMP TABLE](https://docs.pingcap.com/tidbcloudlake/create-temp-table.md): Creates a temporary table that is automatically dropped at the end of the session. - [CREATE TRANSIENT TABLE](https://docs.pingcap.com/tidbcloudlake/create-transient-table.md): Creates a table without storing its historical data for Time Travel. - [DROP TABLE](https://docs.pingcap.com/tidbcloudlake/drop-table.md): Deletes a table. - [UNDROP TABLE](https://docs.pingcap.com/tidbcloudlake/undrop-table.md): Restores the recent version of a dropped table. This leverages the TiDB Cloud Lake Time Travel feature; a dropped object can be restored only within a retention period (defaults to 24 hours). - [RENAME TABLE](https://docs.pingcap.com/tidbcloudlake/rename-table.md): Changes the name of a table. - [TRUNCATE TABLE](https://docs.pingcap.com/tidbcloudlake/truncate-table.md): Removes all data from a table while preserving the table's schema. It deletes all rows in the table, making it an empty table with the same columns and constraints. Please note that, it does not release the disk space allocated to the table. - [DESCRIBE TABLE](https://docs.pingcap.com/tidbcloudlake/describe-table.md): Shows information about the columns in a given table. Equivalent to SHOW FIELDS. - [OPTIMIZE TABLE](https://docs.pingcap.com/tidbcloudlake/optimize-table.md): Optimizing a table in TiDB Cloud Lake involves compacting or purging historical data to save storage space and enhance query performance. - [FLASHBACK TABLE](https://docs.pingcap.com/tidbcloudlake/flashback-table.md): Flashback a table to an earlier version with a snapshot ID or timestamp, only involving metadata operations, making it a fast process. - [ALTER TABLE](https://docs.pingcap.com/tidbcloudlake/alter-table.md): Use ALTER TABLE to modify the structure and properties of an existing table, including its columns, comment, storage options, external connection, or even swapping metadata with another table. The subsections below cover each supported capability. - [VACUUM DROP TABLE](https://docs.pingcap.com/tidbcloudlake/vacuum-drop-table.md): The VACUUM DROP TABLE command helps save storage space by permanently removing data files of dropped tables, freeing up storage space, and enabling you to manage the process efficiently. It offers optional parameters to target specific databases, preview, and limit the number of data files to be vacuumed. To list the dropped tables of a database, use SHOW DROP TABLES. - [VACUUM TABLE](https://docs.pingcap.com/tidbcloudlake/vacuum-table.md): The VACUUM TABLE command helps optimize system performance by freeing up storage space through the permanent removal of historical data files from a table. This includes. - [ATTACH TABLE](https://docs.pingcap.com/tidbcloudlake/attach-table.md): ATTACH TABLE creates a read-only link to existing table data without copying it. - [SHOW CREATE TABLE](https://docs.pingcap.com/tidbcloudlake/show-create-table.md): Shows the CREATE TABLE statement for the specified table. To include the Fuse Engine options in the result, set hide_options_in_show_create_table to 0. - [SHOW DROP TABLES](https://docs.pingcap.com/tidbcloudlake/show-drop-tables.md): Lists the dropped tables in the current or a specified database. - [SHOW FIELDS](https://docs.pingcap.com/tidbcloudlake/show-fields.md): Shows information about the columns in a given table. Equivalent to DESCRIBE TABLE. - [SHOW COLUMNS](https://docs.pingcap.com/tidbcloudlake/show-columns.md): Shows information about the columns in a given table. - [SHOW STATISTICS](https://docs.pingcap.com/tidbcloudlake/show-statistics.md): Displays statistical information about tables and their columns. Statistics help the query optimizer make better decisions about query execution plans by providing information about data distribution, row counts, and distinct values. - [SHOW TABLE STATUS](https://docs.pingcap.com/tidbcloudlake/show-table-status.md): Shows the status of the tables in a database. The status information includes various physical sizes and timestamps about a table, see Examples for details. - [SHOW TABLES](https://docs.pingcap.com/tidbcloudlake/show-tables.md): Lists the tables in the current or a specified database. - View - [Overview](https://docs.pingcap.com/tidbcloudlake/ddl-view-overview.md): This page provides a comprehensive overview of view operations in TiDB Cloud Lake, organized by functionality for easy reference. - [CREATE VIEW](https://docs.pingcap.com/tidbcloudlake/create-view.md): Creates a new view based on a query; the Logical View does not store any physical data, when we access a logical view, it will convert the sql into the subquery format to finish it. - [ALTER VIEW](https://docs.pingcap.com/tidbcloudlake/alter-view.md): Alter the existing view by using another QUERY. - [DESC VIEW](https://docs.pingcap.com/tidbcloudlake/desc-view.md): Returns the list of columns for a view. - [SHOW VIEWS](https://docs.pingcap.com/tidbcloudlake/show-views.md): Returns a list of view names within the specified database, or within the current database if no database name is provided. - [DROP VIEW](https://docs.pingcap.com/tidbcloudlake/drop-view.md): Drop the view. - [Materialized View](https://docs.pingcap.com/tidbcloudlake/materialized-view.md): A materialized view stores the result of a query physically. TiDB Cloud Lake enables change tracking on the source table when the materialized view is created. - [REFRESH LINEAGE](https://docs.pingcap.com/tidbcloudlake/refresh-lineage.md): Backfills or reconciles lineage for existing views in TiDB Cloud Lake. - User & Role - [Overview](https://docs.pingcap.com/tidbcloudlake/user-role.md): This page provides a comprehensive overview of user and role operations in TiDB Cloud Lake, organized by functionality for easy reference. - [CREATE USER](https://docs.pingcap.com/tidbcloudlake/create-user.md): Creates a SQL user for connecting to TiDB Cloud Lake. Users must be granted appropriate privileges to access databases and perform operations. - [DESC USER](https://docs.pingcap.com/tidbcloudlake/desc-user.md): Displays detailed information about a specific SQL user, including authentication type, roles, network policy, password policy, and other user-related settings. - [DROP USER](https://docs.pingcap.com/tidbcloudlake/drop-user.md): Drop the specified user from the system. - [SHOW USERS](https://docs.pingcap.com/tidbcloudlake/show-users.md): Lists all SQL users in the system. If you're using TiDB Cloud Lake, this command also shows the user accounts (email addresses) within your organization that are used to log in to TiDB Cloud Lake. - [ALTER USER](https://docs.pingcap.com/tidbcloudlake/alter-user.md): Modifies a user account, including. - [CREATE ROLE](https://docs.pingcap.com/tidbcloudlake/create-role.md): Creates a new role for access control. Roles are used to group privileges and can be assigned to users or other roles, providing a flexible way to manage permissions in TiDB Cloud Lake. - [SET SECONDARY ROLES](https://docs.pingcap.com/tidbcloudlake/set-secondary-roles.md): Activates all secondary roles for the current session. This means that all secondary roles granted to the user will be active, extending the user's privileges. For more information about the active role and secondary roles, see Active Role & Secondary Roles. - [SET ROLE](https://docs.pingcap.com/tidbcloudlake/set-role.md): Switches the active role for a session, and the currently active role can be viewed using the SHOW ROLES command, with the is_current field indicating the active role. For more information about the active role and secondary roles, see Active Role & Secondary Roles. - [SHOW ROLES](https://docs.pingcap.com/tidbcloudlake/show-roles.md): Lists all the roles assigned to the current user. - [DROP ROLE](https://docs.pingcap.com/tidbcloudlake/drop-role.md): Removes the specified role from the system. - [GRANT](https://docs.pingcap.com/tidbcloudlake/grant.md): Grants privileges, roles, and ownership for a specific database object. This includes. - [REVOKE](https://docs.pingcap.com/tidbcloudlake/revoke.md): Revokes privileges, roles, and ownership of a specific database object. This includes. - [SHOW GRANTS](https://docs.pingcap.com/tidbcloudlake/show-grants.md): Lists privileges granted to roles, role assignments for users, or privileges on a specific object. - Stage - [Overview](https://docs.pingcap.com/tidbcloudlake/stage.md): This page provides a comprehensive overview of stage operations in TiDB Cloud Lake, organized by functionality for easy reference. - [CREATE STAGE](https://docs.pingcap.com/tidbcloudlake/create-stage.md): Creates an internal or external stage. - [DROP STAGE](https://docs.pingcap.com/tidbcloudlake/drop-stage.md): Removes a stage. - [DESC STAGE](https://docs.pingcap.com/tidbcloudlake/desc-stage.md): Describes the properties of a stage. - [LIST STAGE FILES](https://docs.pingcap.com/tidbcloudlake/list-stage-files.md): Lists files in a stage. - [REMOVE STAGE FILES](https://docs.pingcap.com/tidbcloudlake/remove-stage-files.md): Removes files from a stage. - [SHOW STAGES](https://docs.pingcap.com/tidbcloudlake/show-stages.md): Returns a list of the created stages. The output list does not include the user stage. - [PRESIGN](https://docs.pingcap.com/tidbcloudlake/presign.md): Generates the pre-signed URL for a staged file by the stage name and file path you provide. The pre-signed URL enables you to access the file through a web browser or an API request. - Sequence - [Overview](https://docs.pingcap.com/tidbcloudlake/sequence.md): This page provides a comprehensive overview of sequence operations in TiDB Cloud Lake, organized by functionality for easy reference. - [CREATE SEQUENCE](https://docs.pingcap.com/tidbcloudlake/create-sequence.md): Creates a new sequence in TiDB Cloud Lake. - [DESC SEQUENCE](https://docs.pingcap.com/tidbcloudlake/desc-sequence.md): Describes the properties of a sequence. - [DROP SEQUENCE](https://docs.pingcap.com/tidbcloudlake/drop-sequence.md): Deletes an existing sequence from TiDB Cloud Lake. - [SHOW SEQUENCES](https://docs.pingcap.com/tidbcloudlake/show-sequences.md): Returns a list of the created sequences. - Stream - [Overview](https://docs.pingcap.com/tidbcloudlake/stream.md): This page provides a comprehensive overview of stream operations in TiDB Cloud Lake, organized by functionality for easy reference. - [CREATE STREAM](https://docs.pingcap.com/tidbcloudlake/create-stream.md): Creates a stream. - [DESC STREAM](https://docs.pingcap.com/tidbcloudlake/desc-stream.md): Describes the details of a specific stream. - [DROP STREAM](https://docs.pingcap.com/tidbcloudlake/drop-stream.md): Deletes an existing stream. - [SHOW STREAMS](https://docs.pingcap.com/tidbcloudlake/show-streams.md): Lists the streams associated with a specific database. - Task - [Overview](https://docs.pingcap.com/tidbcloudlake/task.md): This page provides a comprehensive overview of task operations in TiDB Cloud Lake, organized by functionality for easy reference. - [CREATE TASK](https://docs.pingcap.com/tidbcloudlake/create-task.md): The CREATE TASK statement is used to define a new task that executes a specified SQL statement on a scheduled basis or dag based task graph. - [ALTER TASK](https://docs.pingcap.com/tidbcloudlake/alter-task.md): The ALTER TASK statement is used to modify an existing task. - [DROP TASK](https://docs.pingcap.com/tidbcloudlake/drop-task.md): The DROP TASK statement is used to delete an existing task. - [EXECUTE TASK](https://docs.pingcap.com/tidbcloudlake/execute-task.md): The EXECUTE TASK statement is used to execute an existing task manually. - [SHOW TASKS](https://docs.pingcap.com/tidbcloudlake/show-tasks.md): Lists the tasks that are visible to the current role. - [TASK ERROR NOTIFICATION PAYLOAD](https://docs.pingcap.com/tidbcloudlake/task-error-notification-payload.md): The payload body for task error notification. - Notification - [Overview](https://docs.pingcap.com/tidbcloudlake/notification.md): This page provides a comprehensive overview of Notification operations in TiDB Cloud Lake, organized by functionality for easy reference. - [CREATE NOTIFICATION INTEGRATION](https://docs.pingcap.com/tidbcloudlake/create-notification-integration.md): Creates a named notification integration that can be used to send notifications to external messaging services. - [ALTER NOTIFICATION INTEGRATION](https://docs.pingcap.com/tidbcloudlake/alter-notification-integration.md): Alter the settings of a named notification integration that can be used to send notifications to external messaging services. - [DROP NOTIFICATION INTEGRATION](https://docs.pingcap.com/tidbcloudlake/drop-notification-integration.md): The DROP NOTIFICATION INTEGRATION statement is used to delete an existing notification. - [DESCRIBE NOTIFICATION INTEGRATION](https://docs.pingcap.com/tidbcloudlake/describe-notification-integration.md): Shows the properties of a notification integration. - Tag - [Overview](https://docs.pingcap.com/tidbcloudlake/tag-overview.md): Overview of tag management and assignment in TiDB Cloud Lake. - [CREATE TAG](https://docs.pingcap.com/tidbcloudlake/create-tag.md): Creates a new tag with optional allowed values and comment. - [DROP TAG](https://docs.pingcap.com/tidbcloudlake/drop-tag.md): Removes a tag. A tag cannot be dropped if it is still referenced by any object. - [SHOW TAGS](https://docs.pingcap.com/tidbcloudlake/show-tags.md): Lists tag definitions in the current tenant. - [SET TAG](https://docs.pingcap.com/tidbcloudlake/set-tag.md): Assigns or removes tags on database objects. - Connection - [Overview](https://docs.pingcap.com/tidbcloudlake/connection.md): A connection in TiDB Cloud Lake refers to a designated configuration that encapsulates the details required to interact with an external storage service. It serves as a centralized and reusable set of parameters, such as access credentials, endpoint URLs, and storage types, facilitating the integration of TiDB Cloud Lake with various storage services. - [CREATE CONNECTION](https://docs.pingcap.com/tidbcloudlake/create-connection.md): Creates a connection to external storage. - [DESC CONNECTION](https://docs.pingcap.com/tidbcloudlake/desc-connection.md): Describes the details of a specific connection, providing information about its type and configuration. - [DROP CONNECTION](https://docs.pingcap.com/tidbcloudlake/drop-connection.md): Deletes an existing connection. - [SHOW CONNECTIONS](https://docs.pingcap.com/tidbcloudlake/show-connections.md): Displays a list of all available connections. - Catalog - [Overview](https://docs.pingcap.com/tidbcloudlake/catalog.md): An overview of Catalog operations in TiDB Cloud Lake. - [SHOW CATALOGS](https://docs.pingcap.com/tidbcloudlake/show-catalogs.md): Lists catalogs. - [SHOW CREATE CATALOG](https://docs.pingcap.com/tidbcloudlake/show-create-catalog.md): Shows catalog definition information. - File Format - [Overview](https://docs.pingcap.com/tidbcloudlake/file-format.md): This page provides a comprehensive overview of File Format operations in TiDB Cloud Lake, organized by functionality for easy reference. - [CREATE FILE FORMAT](https://docs.pingcap.com/tidbcloudlake/create-file-format.md): Create a named file format. - [DROP FILE FORMAT](https://docs.pingcap.com/tidbcloudlake/drop-file-format.md): Removes a file format. - [SHOW FILE FORMATS](https://docs.pingcap.com/tidbcloudlake/show-file-formats.md): Returns a list of created file formats. - Cluster Key - [ALTER CLUSTER KEY](https://docs.pingcap.com/tidbcloudlake/alter-cluster-key.md): Changes the cluster key for a table. - [DROP CLUSTER KEY](https://docs.pingcap.com/tidbcloudlake/drop-cluster-key.md): Deletes the cluster key for a table. - [RECLUSTER TABLE](https://docs.pingcap.com/tidbcloudlake/recluster-table.md): Re-clusters a table. For why and when to re-cluster a table, see Re-clustering Table. - [SET CLUSTER KEY](https://docs.pingcap.com/tidbcloudlake/set-cluster-key.md): Set a cluster key when creating a table. - [Cluster Key](https://docs.pingcap.com/tidbcloudlake/cluster-key.md): This page provides a comprehensive overview of cluster key operations in TiDB Cloud Lake, organized by functionality for easy reference. - Aggregating Index - [CREATE AGGREGATING INDEX](https://docs.pingcap.com/tidbcloudlake/create-aggregating-index.md): Create a new aggregating index in TiDB Cloud Lake. - [DROP AGGREGATING INDEX](https://docs.pingcap.com/tidbcloudlake/drop-aggregating-index.md): Deletes an existing aggregating index. Please note that deleting an aggregating index does NOT remove the associated storage blocks. To delete the blocks as well, use the VACUUM TABLE command. To disable the aggregating indexing feature, set enable_aggregating_index_scan to 0. - [Aggregating Index](https://docs.pingcap.com/tidbcloudlake/aggregating-index-sql.md): This page provides a comprehensive overview of aggregating index operations in TiDB Cloud Lake, organized by functionality for easy reference. - [REFRESH AGGREGATING INDEX](https://docs.pingcap.com/tidbcloudlake/refresh-aggregating-index.md): TiDB Cloud Lake automatically maintains aggregating indexes in SYNC mode as new data is ingested. Run REFRESH AGGREGATING INDEX when you introduce an index on a table that already contains data so earlier rows are backfilled. - Inverted Index - [CREATE INVERTED INDEX](https://docs.pingcap.com/tidbcloudlake/create-inverted-index.md): Creates a new inverted index in TiDB Cloud Lake. - [DROP INVERTED INDEX](https://docs.pingcap.com/tidbcloudlake/drop-inverted-index.md): Removes an inverted index in TiDB Cloud Lake. - [Inverted Index](https://docs.pingcap.com/tidbcloudlake/inverted-index.md): This page provides a comprehensive overview of inverted index operations in TiDB Cloud Lake, organized by functionality for easy reference. - [REFRESH INVERTED INDEX](https://docs.pingcap.com/tidbcloudlake/refresh-inverted-index.md): TiDB Cloud Lake automatically refreshes inverted indexes in SYNC mode whenever new data is written. Use REFRESH INVERTED INDEX primarily to backfill rows that existed before the index was declared. - Ngram Index - [CREATE NGRAM INDEX](https://docs.pingcap.com/tidbcloudlake/create-ngram-index.md): Creates an Ngram index on a column for a table. - [DROP NGRAM INDEX](https://docs.pingcap.com/tidbcloudlake/drop-ngram-index.md): Drops an existing NGRAM index from a table. - [Ngram Index](https://docs.pingcap.com/tidbcloudlake/ngram-index-sql.md): This page provides a comprehensive overview of Ngram index operations in TiDB Cloud Lake, organized by functionality for easy reference. - [REFRESH NGRAM INDEX](https://docs.pingcap.com/tidbcloudlake/refresh-ngram-index.md): TiDB Cloud Lake automatically refreshes NGRAM indexes when data is ingested. Use REFRESH NGRAM INDEX when you need to backfill data that existed before the index was defined. - Spatial Index - [Spatial Index](https://docs.pingcap.com/tidbcloudlake/spatial-index-overview.md): Spatial indexes accelerate spatial predicate filtering on `GEOMETRY` columns. - [CREATE SPATIAL INDEX](https://docs.pingcap.com/tidbcloudlake/create-spatial-index.md): Creates a new spatial index in TiDB Cloud Lake. - [REFRESH SPATIAL INDEX](https://docs.pingcap.com/tidbcloudlake/refresh-spatial-index.md): Refreshes a spatial index to backfill historical rows or update the index after data changes. - [DROP SPATIAL INDEX](https://docs.pingcap.com/tidbcloudlake/drop-spatial-index.md): Removes a spatial index in TiDB Cloud Lake. - Vector Index - [CREATE VECTOR INDEX](https://docs.pingcap.com/tidbcloudlake/create-vector-index.md): Creates a Vector index on a VECTOR column for a table to enable efficient similarity search using the HNSW (Hierarchical Navigable Small World) algorithm. - [DROP VECTOR INDEX](https://docs.pingcap.com/tidbcloudlake/drop-vector-index.md): Removes a Vector index from a table. - [Vector Index](https://docs.pingcap.com/tidbcloudlake/vector-index.md): Vector indexes in TiDB Cloud Lake enable efficient similarity search on high-dimensional vector data using the HNSW (Hierarchical Navigable Small World) algorithm. They support use cases like semantic search, recommendation systems, and AI applications. - [REFRESH VECTOR INDEX](https://docs.pingcap.com/tidbcloudlake/refresh-vector-index.md): Builds a Vector index for existing data that was inserted before the index was created. - Virtual Column - [Virtual Column](https://docs.pingcap.com/tidbcloudlake/virtual-column-overview.md): This page provides a comprehensive overview of virtual column operations in TiDB Cloud Lake, organized by functionality for easy reference. - [REFRESH VIRTUAL COLUMN](https://docs.pingcap.com/tidbcloudlake/refresh-virtual-column.md): The REFRESH VIRTUAL COLUMN command in TiDB Cloud Lake is used to explicitly trigger the creation of virtual columns for existing tables. While TiDB Cloud Lake automatically manages virtual columns for new data, there are specific scenarios where manual refreshing is necessary to take full advantage of this feature. - [SHOW VIRTUAL COLUMNS](https://docs.pingcap.com/tidbcloudlake/show-virtual-columns.md): Shows the created virtual columns in the system. Equivalent to SELECT * FROM system.virtual_columns. - User-Defined Function - [User-Defined Function](https://docs.pingcap.com/tidbcloudlake/user-defined-function.md): User-Defined Functions (UDFs) in TiDB Cloud Lake allow you to create custom operations tailored to your specific data processing needs. This page highlights the commands you will use most often and helps you choose the right function type for your use case. - [Choose a User-Defined Function Type](https://docs.pingcap.com/tidbcloudlake/choose-a-udf-type.md): Learn how to choose a SQL, aggregate, table, or external UDF in TiDB Cloud Lake based on return shape, runtime, and operations. - [ALTER FUNCTION](https://docs.pingcap.com/tidbcloudlake/alter-function.md): Alters a user-defined function. Supports all function types Scalar SQL, Tabular SQL, and Embedded functions. - [CREATE AGGREGATE FUNCTION](https://docs.pingcap.com/tidbcloudlake/create-aggregate-function.md): Creates a user-defined aggregate function (UDAF) that runs inside TiDB Cloud Lake's JavaScript or Python runtime. - [CREATE SCALAR FUNCTION](https://docs.pingcap.com/tidbcloudlake/create-scalar-function.md): Creates a scalar user-defined function (Scalar UDF). The same CREATE FUNCTION statement supports two implementation styles. - [CREATE TABLE FUNCTION](https://docs.pingcap.com/tidbcloudlake/create-table-function.md): Creates a tabular SQL UDF (UDTF) that encapsulates SQL queries as a table function. Table functions are written in SQL; no external languages are involved. - [DROP FUNCTION](https://docs.pingcap.com/tidbcloudlake/drop-function.md): Drops a user-defined function. Works with all function types Scalar SQL, Tabular SQL, and Embedded functions. - [SHOW USER FUNCTIONS](https://docs.pingcap.com/tidbcloudlake/show-user-functions.md): Lists all user-defined functions including scalar functions, table functions, embedded functions, and external functions. - External Function - [External Function](https://docs.pingcap.com/tidbcloudlake/external-function.md): This page provides a comprehensive overview of External Function operations in TiDB Cloud Lake, organized by functionality for easy reference. - [CREATE FUNCTION](https://docs.pingcap.com/tidbcloudlake/create-function.md): Creates an external function that calls a remote handler over Flight (typically Python or other services). - [ALTER FUNCTION](https://docs.pingcap.com/tidbcloudlake/alter-function-sql.md): Alters an external function. - [DROP FUNCTION](https://docs.pingcap.com/tidbcloudlake/drop-function-sql.md): Drops an external function. - Masking Policy - [Overview](https://docs.pingcap.com/tidbcloudlake/masking-policy-sql.md): This page provides a comprehensive overview of Masking Policy operations in TiDB Cloud Lake, organized by functionality for easy reference. - [CREATE MASKING POLICY](https://docs.pingcap.com/tidbcloudlake/create-masking-policy.md): Creates a new masking policy in TiDB Cloud Lake. - [DESC MASKING POLICY](https://docs.pingcap.com/tidbcloudlake/desc-masking-policy.md): Displays detailed information about a specific masking policy in TiDB Cloud Lake. - [DROP MASKING POLICY](https://docs.pingcap.com/tidbcloudlake/drop-masking-policy.md): Deletes an existing masking policy from TiDB Cloud Lake. When you drop a masking policy, it is removed from TiDB Cloud Lake, and its associated masking rules are no longer in effect. Please note that, before dropping a masking policy, ensure that this policy is not associated with any columns. - Network Policy - [ALTER NETWORK POLICY](https://docs.pingcap.com/tidbcloudlake/alter-network-policy.md): Modifies an existing network policy in TiDB Cloud Lake. - [CREATE NETWORK POLICY](https://docs.pingcap.com/tidbcloudlake/create-network-policy.md): Creates a new network policy in TiDB Cloud Lake. - [DESC NETWORK POLICY](https://docs.pingcap.com/tidbcloudlake/desc-network-policy.md): Displays detailed information about a specific network policy in TiDB Cloud Lake. It provides information about the allowed and blocked IP address lists associated with the policy and the comment, if any, that describes the purpose or function of the policy. - [DROP NETWORK POLICY](https://docs.pingcap.com/tidbcloudlake/drop-network-policy.md): Deletes an existing network policy from TiDB Cloud Lake. When you drop a network policy, it is removed from TiDB Cloud Lake, and its associated rules for allowed and blocked IP address lists are no longer in effect. Please note that, before dropping a network policy, ensure that this policy is not associated with any users. - [SHOW NETWORK POLICIES](https://docs.pingcap.com/tidbcloudlake/show-network-policies.md): Displays a list of all existing network policies in TiDB Cloud Lake. It provides information about the available network policies, including their names and whether they have any allowed or blocked IP address lists configured. - [Network Policy](https://docs.pingcap.com/tidbcloudlake/network-policy-sql.md): This page provides a comprehensive overview of Network Policy operations in TiDB Cloud Lake, organized by functionality for easy reference. - Password Policy - [ALTER PASSWORD POLICY](https://docs.pingcap.com/tidbcloudlake/alter-password-policy.md): Modifies an existing password policy in TiDB Cloud Lake. - [CREATE PASSWORD POLICY](https://docs.pingcap.com/tidbcloudlake/create-password-policy.md): Creates a new password policy in TiDB Cloud Lake. - [DESC PASSWORD POLICY](https://docs.pingcap.com/tidbcloudlake/desc-password-policy.md): Displays detailed information about a specific password policy in TiDB Cloud Lake. For detailed descriptions of the password policy attributes, see Password Policy Attributes. - [DROP PASSWORD POLICY](https://docs.pingcap.com/tidbcloudlake/drop-password-policy.md): Deletes an existing password policy from TiDB Cloud Lake. Please note that, before dropping a password policy, ensure that this policy is not associated with any users. - [Password Policy](https://docs.pingcap.com/tidbcloudlake/password-policy-sql.md): This page provides a comprehensive overview of Password Policy operations in TiDB Cloud Lake, organized by functionality for easy reference. - [SHOW PASSWORD POLICIES](https://docs.pingcap.com/tidbcloudlake/show-password-policies.md): Displays a list of all existing password policies in TiDB Cloud Lake. - Row Access Policy - [Overview](https://docs.pingcap.com/tidbcloudlake/row-access-policy-overview.md): A comprehensive overview of Row Access Policy operations in TiDB Cloud Lake, organized by functionality for easy reference. - [CREATE ROW ACCESS POLICY](https://docs.pingcap.com/tidbcloudlake/create-row-access-policy.md): Creates a new row access policy in TiDB Cloud Lake. A row access policy defines a Boolean predicate that TiDB Cloud Lake applies to rows when the policy is attached to a table. - [DESC ROW ACCESS POLICY](https://docs.pingcap.com/tidbcloudlake/desc-row-access-policy.md): Displays detailed information about a specific row access policy in TiDB Cloud Lake. - [DROP ROW ACCESS POLICY](https://docs.pingcap.com/tidbcloudlake/drop-row-access-policy.md): Deletes an existing row access policy from TiDB Cloud Lake. Before dropping a policy, detach it from all tables that reference it. - Dictionary - [Overview](https://docs.pingcap.com/tidbcloudlake/dictionary.md): An overview of dictionary management and information commands. - [CREATE DICTIONARY](https://docs.pingcap.com/tidbcloudlake/create-dictionary.md): Creates a dictionary. - [DROP DICTIONARY](https://docs.pingcap.com/tidbcloudlake/drop-dictionary.md): Deletes a dictionary. - [RENAME DICTIONARY](https://docs.pingcap.com/tidbcloudlake/rename-dictionary.md): Renames a dictionary. - [SHOW CREATE DICTIONARY](https://docs.pingcap.com/tidbcloudlake/show-create-dictionary.md): Shows the SQL statement used to create a dictionary. - [SHOW DICTIONARIES](https://docs.pingcap.com/tidbcloudlake/show-dictionaries.md): Lists dictionaries in the current or specified database. - Pipe - [Overview](https://docs.pingcap.com/tidbcloudlake/pipe.md): Pipe-related SQL commands for ingestion pipelines in TiDB Cloud Lake. - [CREATE PIPE](https://docs.pingcap.com/tidbcloudlake/create-pipe.md): Learn how to use the CREATE PIPE command for ingestion pipelines in TiDB Cloud Lake. - [DESCRIBE PIPE](https://docs.pingcap.com/tidbcloudlake/describe-pipe.md): Learn how to use the DESCRIBE PIPE command to view properties of ingestion pipelines in TiDB Cloud Lake. - [DROP PIPE](https://docs.pingcap.com/tidbcloudlake/drop-pipe.md): Learn how to use the DROP PIPE command to delete ingestion pipelines in TiDB Cloud Lake. - Transaction - [BEGIN](https://docs.pingcap.com/tidbcloudlake/begin.md): Starts a new transaction. BEGIN and COMMIT/ROLLBACK must be used together to start and then either save or undo a transaction. - [COMMIT](https://docs.pingcap.com/tidbcloudlake/commit.md): Saves all changes made during a transaction. BEGIN and COMMIT/ROLLBACK must be used together to start and then either save or undo a transaction. - [Transaction](https://docs.pingcap.com/tidbcloudlake/transaction.md): This page provides a comprehensive overview of Transaction operations in TiDB Cloud Lake, organized by functionality for easy reference. - [ROLLBACK](https://docs.pingcap.com/tidbcloudlake/rollback.md): Undoes all changes made during a transaction. BEGIN and COMMIT/ROLLBACK must be used together to start and then either save or undo a transaction. - [SHOW LOCKS](https://docs.pingcap.com/tidbcloudlake/show-locks.md): Provides a list of active transactions currently holding locks on tables, either for the current user across all their sessions or for all users within the TiDB Cloud Lake system. A lock is a synchronization mechanism that restricts access to shared resources, such as tables, ensuring orderly and controlled interactions among processes or threads within the TiDB Cloud Lake system to maintain data consistency and prevent conflicts. - Variable - [SQL Variables](https://docs.pingcap.com/tidbcloudlake/sql-variables.md): SQL variables allow you to store and manage temporary data within a session, making scripts more dynamic and reusable. - [SET VARIABLE](https://docs.pingcap.com/tidbcloudlake/set-variable.md): Sets the value of one or more SQL variables within a session. The values can be simple constants, expressions, query results, or database objects. Variables persist for the duration of your session and can be used in subsequent queries. - [SHOW VARIABLES](https://docs.pingcap.com/tidbcloudlake/show-variables.md): Displays all session variables and their details, such as names, values, and types. - [UNSET VARIABLE](https://docs.pingcap.com/tidbcloudlake/unset-variable.md): Removes one or more variables from the current session. - Stored Procedure - [CALL PROCEDURE](https://docs.pingcap.com/tidbcloudlake/call-procedure.md): Executes a stored procedure by calling its name, optionally passing arguments if the procedure requires them. - [CREATE PROCEDURE](https://docs.pingcap.com/tidbcloudlake/create-procedure.md): Defines a stored procedure that executes SQL operations and returns a result. - [DESC PROCEDURE](https://docs.pingcap.com/tidbcloudlake/desc-procedure.md): Displays detailed information about a specific stored procedure. - [DROP PROCEDURE](https://docs.pingcap.com/tidbcloudlake/drop-procedure.md): Deletes an existing stored procedure. - [Stored Procedure](https://docs.pingcap.com/tidbcloudlake/stored-procedure.md): This page provides a comprehensive overview of Stored Procedure operations in TiDB Cloud Lake, organized by functionality for easy reference. - [SHOW PROCEDURES](https://docs.pingcap.com/tidbcloudlake/show-procedures.md): Returns a list of all stored procedures in the system. - Warehouse - [Overview](https://docs.pingcap.com/tidbcloudlake/warehouse-overview.md): Warehouse-related SQL commands for TiDB Cloud Lake. - [CREATE WAREHOUSE](https://docs.pingcap.com/tidbcloudlake/create-warehouse.md): Creates a new warehouse for compute resources. - [USE WAREHOUSE](https://docs.pingcap.com/tidbcloudlake/use-warehouse.md): Binds the current session to a specific warehouse. Subsequent queries in the session will use this warehouse for execution. - [SHOW WAREHOUSES](https://docs.pingcap.com/tidbcloudlake/show-warehouses.md): Lists all warehouses visible to the current tenant. - [ALTER WAREHOUSE](https://docs.pingcap.com/tidbcloudlake/alter-warehouse.md): Suspends, resumes, or modifies settings of an existing warehouse. - [ALTER WAREHOUSE ASSIGN NODES](https://docs.pingcap.com/tidbcloudlake/alter-warehouse-assign-nodes.md): Learn how to use the ALTER WAREHOUSE ASSIGN NODES command to assign nodes to clusters in a warehouse in TiDB Cloud Lake. - [ALTER WAREHOUSE UNASSIGN NODES](https://docs.pingcap.com/tidbcloudlake/alter-warehouse-unassign-nodes.md): Learn how to use the ALTER WAREHOUSE UNASSIGN NODES command to remove assigned nodes from clusters in a warehouse in TiDB Cloud Lake. - [DROP WAREHOUSE](https://docs.pingcap.com/tidbcloudlake/drop-warehouse.md): Removes a warehouse and frees up the resources associated with it. - [QUERY_HISTORY](https://docs.pingcap.com/tidbcloudlake/query-history.md): Retrieves query execution logs for analysis and monitoring purposes. - Worker - [Overview](https://docs.pingcap.com/tidbcloudlake/worker-overview.md): Worker-related SQL commands for deployments with cloud control enabled. - [CREATE WORKER](https://docs.pingcap.com/tidbcloudlake/create-worker.md): Creates a worker with an optional key-value option list. - [ALTER WORKER](https://docs.pingcap.com/tidbcloudlake/alter-worker.md): Modify worker tags, options, or state with ALTER WORKER. - [DROP WORKER](https://docs.pingcap.com/tidbcloudlake/drop-worker.md): Remove a worker with DROP WORKER. - [SHOW WORKERS](https://docs.pingcap.com/tidbcloudlake/show-workers.md): List workers and their metadata with SHOW WORKERS. - [Worker Examples](https://docs.pingcap.com/tidbcloudlake/worker-examples.md): Comprehensive examples of using WORKER commands to manage UDF execution environments in TiDB Cloud Lake. - Workload Group - [Workload Group](https://docs.pingcap.com/tidbcloudlake/workload-group.md): Workload groups enable resource management and query concurrency control in TiDB Cloud Lake by allocating CPU, memory quotas and limiting concurrent queries for different user groups. - [ALTER WORKLOAD GROUP](https://docs.pingcap.com/tidbcloudlake/alter-workload-group.md): Update a workload group with specified quota settings. - [CREATE WORKLOAD GROUP](https://docs.pingcap.com/tidbcloudlake/create-workload-group.md): Creates a workload group with specified quota settings. Workload groups control resource allocation and query concurrency by binding to users. When a user submits queries, the workload group limits are applied based on the user's assigned group. - [DROP WORKLOAD GROUP](https://docs.pingcap.com/tidbcloudlake/drop-workload-group.md): Removes the specified workload group. - [RENAME WORKLOAD GROUP](https://docs.pingcap.com/tidbcloudlake/rename-workload-group.md): Renames an existing workload group to a new name. - [SHOW WORKLOAD GROUPS](https://docs.pingcap.com/tidbcloudlake/show-workload-groups.md): Returns a list of all existing workload groups along with their quotas. - Table Versioning - [Overview](https://docs.pingcap.com/tidbcloudlake/table-versioning.md): Table versioning lets you create named references to specific snapshots of a FUSE table. These references survive automatic retention cleanup, giving you stable, human-readable pointers to historical table states. - [CREATE SNAPSHOT TAG](https://docs.pingcap.com/tidbcloudlake/create-snapshot-tag.md): Creates a named snapshot tag on a FUSE table, allowing you to bookmark and query specific points in the table's history. - [DROP SNAPSHOT TAG](https://docs.pingcap.com/tidbcloudlake/drop-snapshot-tag.md): Drops a named snapshot tag from a FUSE table, allowing the referenced snapshot to be garbage collected if no other tags or retention policies protect it. - DML Commands - [DML Overview](https://docs.pingcap.com/tidbcloudlake/dml.md): This page provides reference information for the DML (Data Manipulation Language) commands in TiDB Cloud Lake. - [`COPY INTO `](https://docs.pingcap.com/tidbcloudlake/copy-into-location.md): COPY INTO allows you to unload data from a table or query into one or more files in one of the following locations. - [`COPY INTO `](https://docs.pingcap.com/tidbcloudlake/copy-into-table.md): COPY INTO allows you to load data from files located in one of the following locations. - [DELETE](https://docs.pingcap.com/tidbcloudlake/delete.md): Removes one or more rows from a table. - [INSERT](https://docs.pingcap.com/tidbcloudlake/insert.md): Inserts one or more rows into a table. - [INSERT (multi-table)](https://docs.pingcap.com/tidbcloudlake/insert-multi-table.md): Inserts rows into multiple tables in a single transaction, with the option for the insertion to be dependent on certain conditions (conditionally) or to occur regardless of any conditions (unconditionally). - [MERGE](https://docs.pingcap.com/tidbcloudlake/merge.md): Performs INSERT, UPDATE, or DELETE operations on rows within a target table, all in accordance with conditions and matching criteria specified within the statement, using data from a specified source. - [REPLACE](https://docs.pingcap.com/tidbcloudlake/replace.md): REPLACE INTO can either insert multiple new rows into a table or update existing rows if those rows already exist, using the following sources of data. - [UPDATE](https://docs.pingcap.com/tidbcloudlake/update.md): Updates rows in a table with new values, optionally using values from other tables. - Query Syntax - [Overview](https://docs.pingcap.com/tidbcloudlake/query-syntax.md): This page provides reference information for the query syntax in TiDB Cloud Lake. Each component can be used individually or combined to build powerful queries. - [SELECT](https://docs.pingcap.com/tidbcloudlake/select.md): Retrieves data from a table. - [AT](https://docs.pingcap.com/tidbcloudlake/at.md): The AT clause enables you to retrieve previous versions of your data by specifying a snapshot ID, timestamp, stream name, or a time interval. - [JOIN](https://docs.pingcap.com/tidbcloudlake/join.md): Joins combine columns from two or more tables into one result set. TiDB Cloud Lake implements both ANSI SQL joins and Lake-specific extensions, allowing you to work with dimensional data, slowly changing facts, and time-series streams using the same syntax. - [PIVOT](https://docs.pingcap.com/tidbcloudlake/pivot.md): The PIVOT operation in TiDB Cloud Lake allows you to transform a table by rotating it and aggregating results based on specified columns. - [UNPIVOT](https://docs.pingcap.com/tidbcloudlake/unpivot.md): The UNPIVOT operation rotates a table by transforming columns into rows. - [GROUP BY](https://docs.pingcap.com/tidbcloudlake/group-by.md): TiDB Cloud Lake supports GROUP BY with a variety of extensions. - [CHANGES](https://docs.pingcap.com/tidbcloudlake/changes.md): The CHANGES clause allows querying the change tracking metadata for a table within a defined time interval. Please note that the time interval must fall within the data retention period (defaulted to 24 hours). To define a time interval, use the AT keyword to specify a time point as the start of the interval, with the current time being applied as the default end of the interval. If you wish to specify a past time as the end of the interval, use the END keyword in conjunction with the AT keyword to set the interval. - [QUALIFY](https://docs.pingcap.com/tidbcloudlake/qualify.md): QUALIFY is a clause used to filter the results of a window function. Therefore, to successfully utilize the QUALIFY clause, there must be at least one window function in the SELECT list or the QUALIFY clause (See Examples for each case). In other words, QUALIFY is evaluated after window functions are computed. Here’s the typical order of execution for a query with a QUALIFY statement clause. - [SETTINGS Clause](https://docs.pingcap.com/tidbcloudlake/settings-clause.md): The SETTINGS clause configures specific settings that influence the execution behavior of the SQL statement it precedes. To view the available settings in TiDB Cloud Lake and their values, use SHOW SETTINGS. - [TOP](https://docs.pingcap.com/tidbcloudlake/top.md): Limits the maximum number of rows returned by a query. - [VALUES](https://docs.pingcap.com/tidbcloudlake/values.md): The VALUES clause creates an inline table by explicitly defining rows of data. This temporary table can be used directly or within other SQL statements. - [WITH Clause](https://docs.pingcap.com/tidbcloudlake/clause.md): The WITH clause is an optional clause that precedes the body of the SELECT statement, and defines one or more CTEs (common table expressions) that can be referenced later in the statement. - [WITH CONSUME](https://docs.pingcap.com/tidbcloudlake/with-consume.md): Consumes data from a stream within a SELECT query. - [WITH Stream Hints](https://docs.pingcap.com/tidbcloudlake/stream-hints.md): Specifies various stream configuration options using hints to control how the stream is processed. - Query Operators - [Arithmetic Operators](https://docs.pingcap.com/tidbcloudlake/arithmetic-operators.md): This page describes Arithmetic Operators in TiDB Cloud Lake. - [Comparison Operators](https://docs.pingcap.com/tidbcloudlake/comparison-operators.md): This page describes Comparison Operators in TiDB Cloud Lake. - [Query Operators](https://docs.pingcap.com/tidbcloudlake/query-operators.md): This page provides reference information for the query operators in TiDB Cloud Lake. - [JSON Operators](https://docs.pingcap.com/tidbcloudlake/json-operators.md): This page describes JSON Operators in TiDB Cloud Lake. - [Logical Operators](https://docs.pingcap.com/tidbcloudlake/logical-operators.md): This page describes Logical Operators in TiDB Cloud Lake. - [Set Operators](https://docs.pingcap.com/tidbcloudlake/set-operators-sql.md): Set operators combine the results of two queries into a single result. TiDB Cloud Lake supports the following set operators. - [Subquery Operators](https://docs.pingcap.com/tidbcloudlake/subquery-operators.md): A subquery is a query nested within another one. TiDB Cloud Lake supports the following subquery types. - Explain Commands - [Overview](https://docs.pingcap.com/tidbcloudlake/explain-commands.md): This page provides reference information for the explain-related commands in TiDB Cloud Lake. - [EXPLAIN](https://docs.pingcap.com/tidbcloudlake/explain.md): Shows the execution plan of a SQL statement. An execution plan is shown as a tree consisting of different operators where you can see how TiDB Cloud Lake will execute the SQL statement. An operator usually includes one or more fields describing the actions TiDB Cloud Lake will perform or the objects related to the query. - [EXPLAIN ANALYZE](https://docs.pingcap.com/tidbcloudlake/explain-analyze.md): EXPLAIN ANALYZE used to display a query execution plan along with actual run-time performance statistics. - [EXPLAIN ANALYZE GRAPHICAL](https://docs.pingcap.com/tidbcloudlake/explain-analyze-graphical.md): Analyzes query performance with an interactive visual representation in your browser. Available exclusively in LakeSQL v0.22.2+. - [EXPLAIN AST](https://docs.pingcap.com/tidbcloudlake/explain-ast.md): Returns the abstract syntax tree (AST) of an SQL statement. The command breaks an SQL statement into syntactic parts and represents them in a hierarchical structure. - [EXPLAIN PERF](https://docs.pingcap.com/tidbcloudlake/explain-perf.md): Profiles query CPU usage and returns an HTML flame graph collected from all cluster nodes. - [EXPLAIN RAW](https://docs.pingcap.com/tidbcloudlake/explain-raw.md): Shows the logical execution plan of an SQL statement that you can use to analyze, troubleshoot, and improve the efficiency of your queries. - [EXPLAIN SYNTAX](https://docs.pingcap.com/tidbcloudlake/explain-syntax.md): Outputs formatted SQL code. This command works as an SQL formatter that makes your code easy to read. - Administration Commands - [Overview](https://docs.pingcap.com/tidbcloudlake/administration-commands.md): This page provides reference information for the system administration commands in TiDB Cloud Lake. - [KILL](https://docs.pingcap.com/tidbcloudlake/kill.md): Forcibly terminates the currently running queries. - [SET](https://docs.pingcap.com/tidbcloudlake/set.md): Changes the value of a system setting for the current session. To show all the current settings, use SHOW SETTINGS. - [UNSET](https://docs.pingcap.com/tidbcloudlake/unset.md): Reverts one or more system settings to their global or default levels and values. For more information about the setting levels, see Setting Levels. To show all the current settings, use SHOW SETTINGS. - [SET_VAR](https://docs.pingcap.com/tidbcloudlake/set-var.md): SET_VAR is used to specify optimizer hints within a single SQL statement, allowing for finer control over the execution plan of that specific statement. This includes. - [SHOW SETTINGS](https://docs.pingcap.com/tidbcloudlake/show-settings.md): TiDB Cloud Lake provides a variety of system settings that enable you to control how TiDB Cloud Lake works. This command displays the current and default values, as well as the Setting Levels, of available system settings. To update a setting, use the SET or UNSET command. - [SHOW INDEXES](https://docs.pingcap.com/tidbcloudlake/show-indexes.md): Shows the created indexes. Equivalent to SELECT * FROM system.indexes. - [SHOW FUNCTIONS](https://docs.pingcap.com/tidbcloudlake/show-functions.md): Lists the currently supported built-in scalar and aggregate functions. - [SHOW USER FUNCTIONS](https://docs.pingcap.com/tidbcloudlake/show-user-functions-sql.md): Lists the existing user-defined functions and external functions in the system. Equivalent to SELECT name, is_aggregate, description, arguments, language FROM system.user_functions.... - [SHOW TABLE FUNCTIONS](https://docs.pingcap.com/tidbcloudlake/show-table-functions.md): Shows the list of supported table functions currently. - [SHOW PROCESSLIST](https://docs.pingcap.com/tidbcloudlake/show-processlist.md): The TiDB Cloud Lake process list indicates the operations currently being performed by the set of threads executed within the server. - [SHOW METRICS](https://docs.pingcap.com/tidbcloudlake/show-metrics.md): Shows the list of system metrics. - [VACUUM DROP TABLE](https://docs.pingcap.com/tidbcloudlake/vacuum-drop-table-sql.md): This page describes VACUUM DROP TABLE in TiDB Cloud Lake. - [VACUUM TABLE](https://docs.pingcap.com/tidbcloudlake/vacuum-table-sql.md): This page describes VACUUM TABLE in TiDB Cloud Lake. - [VACUUM TEMPORARY FILES](https://docs.pingcap.com/tidbcloudlake/vacuum-temporary-files.md): Removes temporary files created by TiDB Cloud Lake, such as spill files. - [VACUUM VIRTUAL COLUMN](https://docs.pingcap.com/tidbcloudlake/vacuum-virtual-column.md): Removes obsolete virtual column files for a table. - [EXECUTE IMMEDIATE](https://docs.pingcap.com/tidbcloudlake/execute-immediate.md): Executes a SQL script. For how to write SQL scripts for TiDB Cloud Lake, see Stored Procedure & SQL Scripting. - [SYSTEM FLUSH PRIVILEGES](https://docs.pingcap.com/tidbcloudlake/system-flush-privileges.md): Broadcasts a privilege metadata refresh to all query nodes so GRANT and REVOKE changes take effect immediately. - [SYSTEM ENABLE / DISABLE EXCEPTION_BACKTRACE](https://docs.pingcap.com/tidbcloudlake/system-enable-disable-exception-backtrace.md): Controls the generation of Rust backtraces in TiDB Cloud Lake. SYSTEM ENABLE EXCEPTION_BACKTRACE enables backtraces for debugging purposes when a panic occurs, while SYSTEM DISABLE EXCEPTION_BACKTRACE disables them to avoid additional overhead or exposure of sensitive information. - SQL Functions - [SQL Function Reference](https://docs.pingcap.com/tidbcloudlake/sql-function-reference.md): TiDB Cloud Lake provides comprehensive SQL functions for all types of data processing. Functions are organized by importance and usage frequency. - Bitmap Functions - [BITMAP_AND](https://docs.pingcap.com/tidbcloudlake/bitmap-and.md): Performs a bitwise AND operation on the two bitmaps. - [BITMAP_AND_COUNT](https://docs.pingcap.com/tidbcloudlake/bitmap-and-count.md): Counts the number of bits set to 1 in the bitmap by performing a logical AND operation. - [BITMAP_AND_NOT](https://docs.pingcap.com/tidbcloudlake/bitmap-and-not.md): Alias for BITMAP_NOT. - [BITMAP_CARDINALITY](https://docs.pingcap.com/tidbcloudlake/bitmap-cardinality.md): Alias for BITMAP_COUNT. - [BITMAP_CONTAINS](https://docs.pingcap.com/tidbcloudlake/bitmap-contains.md): Checks if the bitmap contains a specific value. - [BITMAP_COUNT](https://docs.pingcap.com/tidbcloudlake/bitmap-count.md): Counts the number of bits set to 1 in the bitmap. - [BITMAP_HAS_ALL](https://docs.pingcap.com/tidbcloudlake/bitmap-has-all.md): Checks if the first bitmap contains all the bits in the second bitmap. - [BITMAP_HAS_ANY](https://docs.pingcap.com/tidbcloudlake/bitmap-has-any.md): Checks if the first bitmap has any bit matching the bits in the second bitmap. - [BITMAP_INTERSECT](https://docs.pingcap.com/tidbcloudlake/bitmap-intersect.md): Counts the number of bits set to 1 in the bitmap by performing a logical INTERSECT operation. - [BITMAP_MAX](https://docs.pingcap.com/tidbcloudlake/bitmap-max.md): Gets the maximum value in the bitmap. - [BITMAP_MIN](https://docs.pingcap.com/tidbcloudlake/bitmap-min.md): Gets the minimum value in the bitmap. - [BITMAP_NOT](https://docs.pingcap.com/tidbcloudlake/bitmap-not.md): Generates a new bitmap with elements from the first bitmap that are not in the second one. - [BITMAP_NOT_COUNT](https://docs.pingcap.com/tidbcloudlake/bitmap-not-count.md): Counts the number of bits set to 0 in the bitmap by performing a logical NOT operation. - [BITMAP_OR](https://docs.pingcap.com/tidbcloudlake/bitmap-or.md): Performs a bitwise OR operation on the two bitmaps. - [BITMAP_OR_COUNT](https://docs.pingcap.com/tidbcloudlake/bitmap-or-count.md): Counts the number of bits set to 1 in the bitmap by performing a logical OR operation. - [BITMAP_SUBSET_IN_RANGE](https://docs.pingcap.com/tidbcloudlake/bitmap-subset-in-range.md): Generates a sub-bitmap of the source bitmap within a specified range. - [BITMAP_SUBSET_LIMIT](https://docs.pingcap.com/tidbcloudlake/bitmap-subset-limit.md): Generates a sub-bitmap of the source bitmap, beginning with a range from the start value, with a size limit. - [BITMAP_TO_ARRAY](https://docs.pingcap.com/tidbcloudlake/bitmap-array.md): Converts a Bitmap into an Array. - [BITMAP_UNION](https://docs.pingcap.com/tidbcloudlake/bitmap-union.md): Counts the number of bits set to 1 in the bitmap by performing a logical UNION operation. - [BITMAP_XOR](https://docs.pingcap.com/tidbcloudlake/bitmap-xor.md): Performs a bitwise XOR (exclusive OR) operation on the two bitmaps. - [BITMAP_XOR_COUNT](https://docs.pingcap.com/tidbcloudlake/bitmap-xor-count.md): Counts the number of bits set to 1 in the bitmap by performing a logical XOR (exclusive OR) operation. - [Bitmap Functions](https://docs.pingcap.com/tidbcloudlake/bitmap-functions.md): This page provides a comprehensive overview of Bitmap functions in TiDB Cloud Lake, organized by functionality for easy reference. - [INTERSECT_COUNT](https://docs.pingcap.com/tidbcloudlake/intersect-count.md): Counts the number of intersecting bits between two bitmap columns. - [SUB_BITMAP](https://docs.pingcap.com/tidbcloudlake/sub-bitmap.md): Generates a sub-bitmap of the source bitmap, beginning from the start index, with a specified size. - Conversion Functions - [BUILD_BITMAP](https://docs.pingcap.com/tidbcloudlake/build-bitmap.md): Converts an array of positive integers to a BITMAP value. - [CAST::](https://docs.pingcap.com/tidbcloudlake/cast.md): Converts a value from one data type to another. is an alias for CAST. - [Conversion Functions](https://docs.pingcap.com/tidbcloudlake/conversion-functions.md): This page provides a comprehensive overview of Conversion functions in TiDB Cloud Lake, organized by functionality for easy reference. - [TO_BINARY](https://docs.pingcap.com/tidbcloudlake/to-binary.md): Converts supported data types, including string, variant, bitmap, geometry, and geography, into their binary representation (hex format). - [TO_BITMAP](https://docs.pingcap.com/tidbcloudlake/to-bitmap.md): Converts a value to BITMAP data type. - [TO_BOOLEAN](https://docs.pingcap.com/tidbcloudlake/to-boolean.md): Converts a value to BOOLEAN data type. - [TO_DECIMAL](https://docs.pingcap.com/tidbcloudlake/to-decimal.md): Converts a value to DECIMAL data type. - [TO_FLOAT32](https://docs.pingcap.com/tidbcloudlake/to-float32.md): Converts a value to FLOAT32 data type. - [TO_FLOAT64](https://docs.pingcap.com/tidbcloudlake/to-float64.md): Converts a value to FLOAT64 data type. - [TO_HEX](https://docs.pingcap.com/tidbcloudlake/to-hex.md): For a string argument str, TO_HEX() returns a hexadecimal string representation of str where each byte of each character in str is converted to two hexadecimal digits. The inverse of this operation is performed by the UNHEX() function. - [TO_INT16](https://docs.pingcap.com/tidbcloudlake/to-int16.md): Converts a value to INT16 data type. - [TO_INT32](https://docs.pingcap.com/tidbcloudlake/to-int32.md): Converts a value to INT32 data type. - [TO_INT64](https://docs.pingcap.com/tidbcloudlake/to-int64.md): Converts a value to INT64 data type. - [TO_INT8](https://docs.pingcap.com/tidbcloudlake/to-int8.md): Converts a value to INT8 data type. - [TO_STRING](https://docs.pingcap.com/tidbcloudlake/to-string.md): Converts a value to String data type, or converts a Date value to a specific string format. To customize the format of date and time in TiDB Cloud Lake, you can utilize specifiers. These specifiers allow you to define the desired format for date and time values. For a comprehensive list of supported specifiers, see Formatting Date and Time. - [TO_TEXT](https://docs.pingcap.com/tidbcloudlake/to-text.md): Alias for TO_STRING. - [TO_UINT16](https://docs.pingcap.com/tidbcloudlake/to-uint16.md): Converts a value to UINT16 data type. - [TO_UINT32](https://docs.pingcap.com/tidbcloudlake/to-uint32.md): Converts a value to UINT32 data type. - [TO_UINT64](https://docs.pingcap.com/tidbcloudlake/to-uint64.md): Converts a value to UINT64 data type. - [TO_UINT8](https://docs.pingcap.com/tidbcloudlake/to-uint8.md): Converts a value to UINT8 data type. - [TO_VARCHAR](https://docs.pingcap.com/tidbcloudlake/to-varchar.md): Alias for TO_STRING. - [TO_VARIANT](https://docs.pingcap.com/tidbcloudlake/to-variant.md): Converts a value to VARIANT data type. - [TRY_CAST](https://docs.pingcap.com/tidbcloudlake/try-cast.md): Converts a value from one data type to another. Returns NULL on error. - [TRY_TO_BINARY](https://docs.pingcap.com/tidbcloudlake/try-to-binary.md): An enhanced version of TO_BINARY that converts an input expression to a binary value, returning NULL if the conversion fails instead of raising an error. - Conditional Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/conditional-functions.md): This page provides a comprehensive overview of Conditional functions in TiDB Cloud Lake, organized by functionality for easy reference. - [[ NOT ] BETWEEN](https://docs.pingcap.com/tidbcloudlake/between.md): Returns true if the given numeric or string falls inside the defined lower and upper limits. - [CASE](https://docs.pingcap.com/tidbcloudlake/case.md): Handles IF/THEN logic. It is structured with at least one pair of WHEN and THEN statements. Every CASE statement must be concluded with the END keyword. The ELSE statement is optional, providing a way to capture values not explicitly specified in the WHEN and THEN statements. - [COALESCE](https://docs.pingcap.com/tidbcloudlake/coalesce.md): Returns the first non-NULL expression within its arguments; if all arguments are NULL, it returns NULL. - [DECODE](https://docs.pingcap.com/tidbcloudlake/decode.md): The DECODE function compares the select expression to each search expression in order. As soon as a search expression matches the selection expression, the corresponding result expression is returned. If no match is found and a default value is provided, the default value is returned. - [ERROR_OR](https://docs.pingcap.com/tidbcloudlake/error-or.md): Returns the first non-error expression among its inputs. If all expressions result in errors, it returns NULL. - [GREATEST](https://docs.pingcap.com/tidbcloudlake/greatest.md): Returns the maximum value from a set of values. If any value in the set is NULL, the function returns NULL. - [GREATEST_IGNORE_NULLS](https://docs.pingcap.com/tidbcloudlake/greatest-ignore-nulls.md): Returns the maximum value from a set of values, ignoring any NULL values. - [IF](https://docs.pingcap.com/tidbcloudlake/if.md): If is TRUE, it returns . Otherwise if is TRUE, it returns , and so on. - [IFF](https://docs.pingcap.com/tidbcloudlake/iff.md): Alias for IF. - [IFNULL](https://docs.pingcap.com/tidbcloudlake/ifnull.md): If is NULL, returns , otherwise returns . - [[ NOT ] IN](https://docs.pingcap.com/tidbcloudlake/in.md): Checks whether a value is (or is not) in an explicit list. - [IS [ NOT ] DISTINCT FROM](https://docs.pingcap.com/tidbcloudlake/is-distinct-from.md): Compares whether two expressions are equal (or not equal) with awareness of nullability, meaning it treats NULLs as known values for comparing equality. - [IS_ERROR](https://docs.pingcap.com/tidbcloudlake/is-error.md): Returns a Boolean value indicating whether an expression is an error value. - [IS_NOT_ERROR](https://docs.pingcap.com/tidbcloudlake/is-not-error.md): Returns a Boolean value indicating whether an expression is an error value. - [IS_NOT_NULL](https://docs.pingcap.com/tidbcloudlake/is-not-null.md): Checks whether a value is not NULL. - [IS_NULL](https://docs.pingcap.com/tidbcloudlake/is-null.md): Checks whether a value is NULL. - [LEAST](https://docs.pingcap.com/tidbcloudlake/least.md): Returns the minimum value from a set of values. If any value in the set is NULL, the function returns NULL. - [LEAST_IGNORE_NULLS](https://docs.pingcap.com/tidbcloudlake/least-ignore-nulls.md): Returns the maximum value from a set of values, ignoring any NULL values. - [NULLIF](https://docs.pingcap.com/tidbcloudlake/nullif.md): Returns NULL if two expressions are equal. Otherwise return expr1. They must have the same data type. - [NVL](https://docs.pingcap.com/tidbcloudlake/nvl.md): If is NULL, returns , otherwise returns . - [NVL2](https://docs.pingcap.com/tidbcloudlake/nvl2.md): Returns if is not NULL; otherwise, it returns . - Numeric Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/numeric-functions.md): This page provides a comprehensive overview of Numeric functions in TiDB Cloud Lake, organized by functionality for easy reference. - [ABS](https://docs.pingcap.com/tidbcloudlake/abs.md): Returns the absolute value of x. - [ACOS](https://docs.pingcap.com/tidbcloudlake/acos.md): Returns the arc cosine of x, that is, the value whose cosine is x. Returns NULL if x is not in the range -1 to 1. - [ADD](https://docs.pingcap.com/tidbcloudlake/add.md): Alias for PLUS. - [ASIN](https://docs.pingcap.com/tidbcloudlake/asin.md): Returns the arc sine of x, that is, the value whose sine is x. Returns NULL if x is not in the range -1 to 1. - [ATAN](https://docs.pingcap.com/tidbcloudlake/atan.md): Returns the arc tangent of x, that is, the value whose tangent is x. - [ATAN2](https://docs.pingcap.com/tidbcloudlake/atan-sql.md): Returns the arc tangent of the two variables x and y. It is similar to calculating the arc tangent of y / x, except that the signs of both arguments are used to determine the quadrant of the result. ATAN(y, x) is a synonym for ATAN2(y, x). - [CBRT](https://docs.pingcap.com/tidbcloudlake/cbrt.md): Returns the cube root of a nonnegative number x. - [CEIL](https://docs.pingcap.com/tidbcloudlake/ceil.md): Rounds the number up. - [CEILING](https://docs.pingcap.com/tidbcloudlake/ceiling.md): Alias for CEIL. - [COS](https://docs.pingcap.com/tidbcloudlake/cos.md): Returns the cosine of x, where x is given in radians. - [COT](https://docs.pingcap.com/tidbcloudlake/cot.md): Returns the cotangent of x, where x is given in radians. - [CRC32](https://docs.pingcap.com/tidbcloudlake/crc.md): Returns the CRC32 checksum of x, where 'x' is expected to be a string and (if possible) is treated as one if it is not. - [DEGREES](https://docs.pingcap.com/tidbcloudlake/degrees.md): Returns the argument x, converted from radians to degrees, where x is given in radians. - [DIV](https://docs.pingcap.com/tidbcloudlake/div.md): Returns the quotient by dividing the first number by the second one, rounding down to the closest smaller integer. Equivalent to the division operator //. - [DIV0](https://docs.pingcap.com/tidbcloudlake/div0.md): Returns the quotient by dividing the first number by the second one. Returns 0 if the second number is 0. - [DIVNULL](https://docs.pingcap.com/tidbcloudlake/divnull.md): Returns the quotient by dividing the first number by the second one. Returns NULL if the second number is 0 or NULL. - [EXP](https://docs.pingcap.com/tidbcloudlake/exp.md): Returns the value of e (the base of natural logarithms) raised to the power of x. - [FACTORIAL](https://docs.pingcap.com/tidbcloudlake/factorial.md): Returns the factorial logarithm of x. If x is less than or equal to 0, the function returns 0. - [FLOOR](https://docs.pingcap.com/tidbcloudlake/floor.md): Rounds the number down. - [INTDIV](https://docs.pingcap.com/tidbcloudlake/intdiv.md): Alias for DIV. - [LN](https://docs.pingcap.com/tidbcloudlake/ln.md): Returns the natural logarithm of x; that is, the base-e logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL. - [LOG10](https://docs.pingcap.com/tidbcloudlake/log.md): Returns the base-10 logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL. - [LOG2](https://docs.pingcap.com/tidbcloudlake/log-sql.md): Returns the base-2 logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL. - [LOG(b, x)](https://docs.pingcap.com/tidbcloudlake/log-b-x.md): Returns the base-b logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL. - [LOG(x)](https://docs.pingcap.com/tidbcloudlake/log-x.md): Returns the natural logarithm of x. If x is less than or equal to 0.0E0, the function returns NULL. - [MINUS](https://docs.pingcap.com/tidbcloudlake/minus.md): Negates a numeric value. - [MOD](https://docs.pingcap.com/tidbcloudlake/mod.md): Alias for MODULO. - [MODULO](https://docs.pingcap.com/tidbcloudlake/modulo.md): Returns the remainder of x divided by y. If y is 0, it returns an error. - [MULTIPLY](https://docs.pingcap.com/tidbcloudlake/multiply.md): The MULTIPLY function performs multiplication between two numbers. It is equivalent to using the * operator. - [NEG](https://docs.pingcap.com/tidbcloudlake/neg.md): Alias for MINUS. - [NEGATE](https://docs.pingcap.com/tidbcloudlake/negate.md): Alias for MINUS. - [PI](https://docs.pingcap.com/tidbcloudlake/pi.md): Returns the value of π as a floating-point value. - [PLUS](https://docs.pingcap.com/tidbcloudlake/plus.md): Calculates the sum of two numeric or decimal values. - [POW](https://docs.pingcap.com/tidbcloudlake/pow.md): Returns the value of x to the power of y. - [POWER](https://docs.pingcap.com/tidbcloudlake/power.md): Alias for POW. - [RADIANS](https://docs.pingcap.com/tidbcloudlake/radians.md): Returns the argument x, converted from degrees to radians. - [RAND()](https://docs.pingcap.com/tidbcloudlake/rand.md): Returns a random floating-point value v in the range 0 <= v < 1.0. To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j − i)). - [RAND(n)](https://docs.pingcap.com/tidbcloudlake/rand-n.md): Returns a random floating-point value v in the range 0 <= v < 1.0. To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j − i)). Argument n is used as the seed value. For equal argument values, RAND(n) returns the same value each time, and thus produces a repeatable sequence of column values. - [ROUND](https://docs.pingcap.com/tidbcloudlake/round.md): Rounds the argument x to d decimal places. The rounding algorithm depends on the data type of x. d defaults to 0 if not specified. d can be negative to cause d digits left of the decimal point of the value x to become zero. The maximum absolute value for d is 30; any digits in excess of 30 (or -30) are truncated. - [SIGN](https://docs.pingcap.com/tidbcloudlake/sign.md): Returns the sign of the argument as -1, 0, or 1, depending on whether x is negative, zero, or positive or NULL if the argument was NULL. - [SIN](https://docs.pingcap.com/tidbcloudlake/sin.md): Returns the sine of x, where x is given in radians. - [SQRT](https://docs.pingcap.com/tidbcloudlake/sqrt.md): Returns the square root of a nonnegative number x. Returns Nan for negative input. - [SUBTRACT](https://docs.pingcap.com/tidbcloudlake/subtract.md): Alias for MINUS. - [TAN](https://docs.pingcap.com/tidbcloudlake/tan.md): Returns the tangent of x, where x is given in radians. - [TRUNC](https://docs.pingcap.com/tidbcloudlake/trunc.md): Returns the number x, truncated to d decimal places. If d is 0, the result has no decimal point or fractional part. d can be negative to cause d digits left of the decimal point of the value x to become zero. The maximum absolute value for d is 30; any digits in excess of 30 (or -30) are truncated. - [TRUNCATE](https://docs.pingcap.com/tidbcloudlake/truncate.md): Returns the number x, truncated to d decimal places. If d is 0, the result has no decimal point or fractional part. d can be negative to cause d digits left of the decimal point of the value x to become zero. The maximum absolute value for d is 30; any digits in excess of 30 (or -30) are truncated. - Date & Time Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/date-time-functions.md): This page provides a comprehensive overview of Date & Time functions in TiDB Cloud Lake, organized by functionality for easy reference. - [ADD_MONTHS](https://docs.pingcap.com/tidbcloudlake/add-months.md): The add_months() function adds a specified number of months to a given date or timestamp. - [ADD TIME INTERVAL](https://docs.pingcap.com/tidbcloudlake/add-interval.md): Add a time interval to a date or timestamp, return the result of date or timestamp type. - [AGE](https://docs.pingcap.com/tidbcloudlake/age.md): The age() function calculates the difference between two timestamps or the difference between a timestamp and the current date and time. - [CONVERT_TIMEZONE](https://docs.pingcap.com/tidbcloudlake/convert-timezone.md): Converts a timestamp from one timezone to another and requires a valid IANA destination timezone name. - [CURRENT_TIMESTAMP](https://docs.pingcap.com/tidbcloudlake/current-timestamp.md): Alias for NOW. - [DATE](https://docs.pingcap.com/tidbcloudlake/date.md): Alias for TO_DATE. - [DATE_ADD](https://docs.pingcap.com/tidbcloudlake/date-add.md): Adds a specified time interval to a DATE or TIMESTAMP value. - [DATE_BETWEEN](https://docs.pingcap.com/tidbcloudlake/date-between.md): Calculates the time interval between two dates or timestamps, returning the difference as an integer in the specified unit, with positive values indicating the first time is earlier than the second, and negative values indicating the opposite. - [DATE_DIFF](https://docs.pingcap.com/tidbcloudlake/date-diff.md): Calculates the difference between two dates or timestamps based on a specified time unit. The result is positive if the is after the , and negative if it's before. - [DATE_FORMAT](https://docs.pingcap.com/tidbcloudlake/date-format.md): Alias for TO_STRING. - [DATE_PART](https://docs.pingcap.com/tidbcloudlake/date-part.md): Retrieves the designated portion of a date or timestamp. - [DATE_SUB](https://docs.pingcap.com/tidbcloudlake/date-sub.md): Subtract the time interval or date interval from the provided date or date with time (timestamp/datetime). - [DATE_TRUNC](https://docs.pingcap.com/tidbcloudlake/date-trunc.md): Truncates a date or timestamp to a specified precision, providing a standardized way to manipulate dates and timestamps. This function is designed to be compatible with various database systems, making it easier for users to migrate and work with different databases. - [DAY](https://docs.pingcap.com/tidbcloudlake/day.md): Alias for TO_DAY_OF_MONTH. - [EXTRACT](https://docs.pingcap.com/tidbcloudlake/extract.md): Retrieves the designated portion of a date, timestamp, or interval. - [LAST_DAY](https://docs.pingcap.com/tidbcloudlake/last-day.md): Returns the last day of the specified interval (week, month, quarter, or year) based on the provided date or timestamp. - [MILLENNIUM](https://docs.pingcap.com/tidbcloudlake/millennium.md): Returns the millennium of a given date or timestamp. The 1st millennium spans years 0001–1000, the 2nd spans 1001–2000, the 3rd spans 2001–3000, and so on. - [MONTH](https://docs.pingcap.com/tidbcloudlake/month.md): Alias for TO_MONTH. - [MONTHS_BETWEEN](https://docs.pingcap.com/tidbcloudlake/months-between.md): Returns the number of months between *date1* and *date2*. - [NEXT_DAY](https://docs.pingcap.com/tidbcloudlake/next-day.md): Returns the date of the upcoming specified day of the week after the given date or timestamp. - [NOW](https://docs.pingcap.com/tidbcloudlake/now.md): Returns the current date and time. - [PREVIOUS_DAY](https://docs.pingcap.com/tidbcloudlake/previous-day.md): Returns the date of the most recent specified day of the week before the given date or timestamp. - [QUARTER](https://docs.pingcap.com/tidbcloudlake/quarter.md): Alias for TO_QUARTER. - [STR_TO_DATE](https://docs.pingcap.com/tidbcloudlake/str-to-date.md): Alias for TO_DATE. - [STR_TO_TIMESTAMP](https://docs.pingcap.com/tidbcloudlake/str-to-timestamp.md): Alias for TO_TIMESTAMP. - [SUBTRACT TIME INTERVAL](https://docs.pingcap.com/tidbcloudlake/subtract-interval.md): Subtract time interval function. - [TIME_SLICE](https://docs.pingcap.com/tidbcloudlake/time-slice.md): TIME_SLICE is a scalar function used to map a single date/timestamp value to a fixed calendar interval (slice or bucket). - [TIME_SLOT](https://docs.pingcap.com/tidbcloudlake/time-slot.md): Rounds the time to the half-hour. ## Syntax. - [TIMESTAMP_DIFF](https://docs.pingcap.com/tidbcloudlake/timestamp-diff.md): Calculates the difference between two timestamps and returns the result as an INTERVAL. - [TIMEZONE](https://docs.pingcap.com/tidbcloudlake/timezone.md): Returns the timezone for the current connection. - [TO_DATE](https://docs.pingcap.com/tidbcloudlake/to-date.md): Converts an expression to a date, including. - [TO_DATETIME](https://docs.pingcap.com/tidbcloudlake/datetime.md): Alias for TO_TIMESTAMP. - [TO_DAY_OF_MONTH](https://docs.pingcap.com/tidbcloudlake/to-day-of-month.md): Convert a date or date with time (timestamp/datetime) to a UInt8 number containing the number of the day of the month (1-31). - [TO_DAY_OF_WEEK](https://docs.pingcap.com/tidbcloudlake/day-week.md): Converts a date or date with time (timestamp/datetime) to a UInt8 number containing the number of the day of the week (Monday is 1, and Sunday is 7). - [TO_DAY_OF_YEAR](https://docs.pingcap.com/tidbcloudlake/day-year.md): Convert a date or date with time (timestamp/datetime) to a UInt16 number containing the number of the day of the year (1-366). - [TO_HOUR](https://docs.pingcap.com/tidbcloudlake/hour.md): Converts a date with time (timestamp/datetime) to a UInt8 number containing the number of the hour in 24-hour time (0-23). This function assumes that if clocks are moved ahead, it is by one hour and occurs at 2 a.m., and if clocks are moved back, it is by one hour and occurs at 3 a.m. (which is not always true – even in Moscow the clocks were twice changed at a different time). - [TO_MINUTE](https://docs.pingcap.com/tidbcloudlake/minute.md): Converts a date with time (timestamp/datetime) to a UInt8 number containing the number of the minute of the hour (0-59). - [TO_MONDAY](https://docs.pingcap.com/tidbcloudlake/monday.md): Round down a date or date with time (timestamp/datetime) to the nearest Monday. Returns the date. - [TO_MONTH](https://docs.pingcap.com/tidbcloudlake/to-month.md): Convert a date or date with time (timestamp/datetime) to a UInt8 number containing the month number (1-12). - [TO_QUARTER](https://docs.pingcap.com/tidbcloudlake/to-quarter.md): Retrieves the quarter (1, 2, 3, or 4) from a given date or timestamp. - [TO_SECOND](https://docs.pingcap.com/tidbcloudlake/second.md): Converts a date with time (timestamp/datetime) to a UInt8 number containing the number of the second in the minute (0-59). - [TO_START_OF_DAY](https://docs.pingcap.com/tidbcloudlake/to-start-of-day.md): Rounds down a date with time (timestamp/datetime) to the start of the day. ## Syntax. - [TO_START_OF_FIFTEEN_MINUTES](https://docs.pingcap.com/tidbcloudlake/start-fifteen-minutes.md): Rounds down the date with time (timestamp/datetime) to the start of the fifteen-minute interval. ## Syntax. - [TO_START_OF_FIVE_MINUTES](https://docs.pingcap.com/tidbcloudlake/start-five-minutes.md): Rounds down a date with time (timestamp/datetime) to the start of the five-minute interval. ## Syntax. - [TO_START_OF_HOUR](https://docs.pingcap.com/tidbcloudlake/to-start-of-hour.md): Rounds down a date with time (timestamp/datetime) to the start of the hour. ## Syntax. - [TO_START_OF_ISO_YEAR](https://docs.pingcap.com/tidbcloudlake/start-iso-year.md): Returns the first day of the ISO year for a date or a date with time (timestamp/datetime). - [TO_START_OF_MINUTE](https://docs.pingcap.com/tidbcloudlake/to-start-of-minute.md): Rounds down a date with time (timestamp/datetime) to the start of the minute. - [TO_START_OF_MONTH](https://docs.pingcap.com/tidbcloudlake/to-start-of-month.md): Rounds down a date or date with time (timestamp/datetime) to the first day of the month. Returns the date. - [TO_START_OF_QUARTER](https://docs.pingcap.com/tidbcloudlake/to-start-of-quarter.md): Rounds down a date or date with time (timestamp/datetime) to the first day of the quarter. The first day of the quarter is either 1 January, 1 April, 1 July, or 1 October. Returns the date. - [TO_START_OF_SECOND](https://docs.pingcap.com/tidbcloudlake/to-start-of-second.md): Rounds down a date with time (timestamp/datetime) to the start of the second. - [TO_START_OF_TEN_MINUTES](https://docs.pingcap.com/tidbcloudlake/start-ten-minutes.md): Rounds down a date with time (timestamp/datetime) to the start of the ten-minute interval. - [TO_START_OF_WEEK](https://docs.pingcap.com/tidbcloudlake/to-start-of-week.md): Returns the first day of the week for a date or a date with time (timestamp/datetime). The first day of a week can be Sunday or Monday, which is specified by the argument mode. - [TO_START_OF_YEAR](https://docs.pingcap.com/tidbcloudlake/to-start-of-year.md): Returns the first day of the year for a date or a date with time (timestamp/datetime). - [TO_TIMESTAMP](https://docs.pingcap.com/tidbcloudlake/to-timestamp.md): Converts an expression to a date with time. - [TO_TIMESTAMP_TZ](https://docs.pingcap.com/tidbcloudlake/timestamp-tz.md): Converts a value to TIMESTAMP_TZ, keeping both the UTC moment and the timezone offset. Use TRY_TO_TIMESTAMP_TZ if you prefer NULL instead of an error. - [TO_UNIX_TIMESTAMP](https://docs.pingcap.com/tidbcloudlake/unix-timestamp.md): Converts a timestamp in a date/time format to a Unix timestamp format. A Unix timestamp represents the number of seconds that have elapsed since January 1, 1970, at 000000 UTC. - [TO_WEEK_OF_YEAR](https://docs.pingcap.com/tidbcloudlake/to-week-of-year.md): Calculates the week number within a year for a given date. - [TO_YEAR](https://docs.pingcap.com/tidbcloudlake/to-year.md): Converts a date or date with time (timestamp/datetime) to a UInt16 number containing the year number (AD). - [TO_YYYYMM](https://docs.pingcap.com/tidbcloudlake/yyyymm.md): Converts a date or date with time (timestamp/datetime) to a UInt32 number containing the year and month number. - [TO_YYYYMMDD](https://docs.pingcap.com/tidbcloudlake/yyyymmdd.md): Converts a date or date with time (timestamp/datetime) to a UInt32 number containing the year and month number (YYYY * 10000 + MM * 100 + DD). ## Syntax. - [TO_YYYYMMDDHH](https://docs.pingcap.com/tidbcloudlake/yyyymmddhh.md): Formats a given date or timestamp into a string representation in the format "YYYYMMDDHH" (Year, Month, Day, Hour). - [TO_YYYYMMDDHHMMSS](https://docs.pingcap.com/tidbcloudlake/yyyymmddhhmmss.md): Convert a date or date with time (timestamp/datetime) to a UInt64 number containing the year and month number (YYYY * 10000000000 + MM * 100000000 + DD * 1000000 + hh * 10000 + mm * 100 + ss). - [TODAY](https://docs.pingcap.com/tidbcloudlake/today.md): Returns current date. - [TOMORROW](https://docs.pingcap.com/tidbcloudlake/tomorrow.md): Returns tomorrow date, same as today() + 1. - [TRUNC](https://docs.pingcap.com/tidbcloudlake/trunc-sql.md): Truncates a date or timestamp to a specified precision. This function follows a widely adopted date truncation syntax, making it easier for users migrating from other database systems. - [TRY_TO_DATETIME](https://docs.pingcap.com/tidbcloudlake/try-to-datetime.md): Alias for TRY_TO_TIMESTAMP. - [TRY_TO_TIMESTAMP](https://docs.pingcap.com/tidbcloudlake/try-to-timestamp.md): A variant of TO_TIMESTAMP in TiDB Cloud Lake that, while performing the same conversion of an input expression to a timestamp, incorporates error-handling support by returning NULL if the conversion fails instead of raising an error. - [WEEK](https://docs.pingcap.com/tidbcloudlake/week.md): Alias for TO_WEEK_OF_YEAR. - [WEEKOFYEAR](https://docs.pingcap.com/tidbcloudlake/weekofyear.md): Alias for TO_WEEK_OF_YEAR. - [YEAR](https://docs.pingcap.com/tidbcloudlake/year.md): Alias for TO_YEAR. - [YEARWEEK](https://docs.pingcap.com/tidbcloudlake/yearweek.md): Returns the year and week number in YYYYWW format, according to ISO week date. Week 1 is the week with the first Thursday of the year. - [YESTERDAY](https://docs.pingcap.com/tidbcloudlake/yesterday.md): Returns yesterday date, same as today() - 1. - Interval Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/interval-functions.md): This section provides reference information for the interval functions in TiDB Cloud Lake. Interval functions allow you to create interval values of various time units for use in date and time calculations. - [EPOCH](https://docs.pingcap.com/tidbcloudlake/epoch.md): Alias for TO_SECONDS. - [TO_CENTURIES](https://docs.pingcap.com/tidbcloudlake/to-centuries.md): Converts a specified number of centuries into an Interval type. - [TO_DAYS](https://docs.pingcap.com/tidbcloudlake/days.md): Converts a specified number of days into an Interval type. - [TO_DECADES](https://docs.pingcap.com/tidbcloudlake/decades.md): Converts a specified number of decades into an Interval type. - [TO_HOURS](https://docs.pingcap.com/tidbcloudlake/hours.md): Converts a specified number of hours into an Interval type. - [TO_MICROSECONDS](https://docs.pingcap.com/tidbcloudlake/microseconds.md): Converts a specified number of microseconds into an Interval type. - [TO_MILLENNIA](https://docs.pingcap.com/tidbcloudlake/millennia.md): Converts a specified number of millennia into an Interval type. - [TO_MILLISECONDS](https://docs.pingcap.com/tidbcloudlake/milliseconds.md): Converts a specified number of milliseconds into an Interval type. - [TO_MINUTES](https://docs.pingcap.com/tidbcloudlake/minutes.md): Converts a specified number of minutes into an Interval type. - [TO_MONTHS](https://docs.pingcap.com/tidbcloudlake/months.md): Converts a specified number of months into an Interval type. - [TO_SECONDS](https://docs.pingcap.com/tidbcloudlake/seconds.md): Converts a specified number of seconds into an Interval type. - [TO_WEEKS](https://docs.pingcap.com/tidbcloudlake/weeks.md): Converts a specified number of weeks into an Interval type. - [TO_YEARS](https://docs.pingcap.com/tidbcloudlake/years.md): Converts a specified number of years into an Interval type. - String Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/string-functions-overview.md): This page provides a comprehensive overview of String functions in TiDB Cloud Lake, organized by functionality for easy reference. - [ASCII](https://docs.pingcap.com/tidbcloudlake/ascii.md): Returns the numeric value of the leftmost character of the string str. - [BIN](https://docs.pingcap.com/tidbcloudlake/bin.md): Returns a string representation of the binary value of N. - [BIT_LENGTH](https://docs.pingcap.com/tidbcloudlake/bit-length.md): Return the length of a string in bits. - [CHAR](https://docs.pingcap.com/tidbcloudlake/char.md): Returns the character(s) for each integer passed. The function converts each integer to its corresponding Unicode character. - [CHAR_LENGTH](https://docs.pingcap.com/tidbcloudlake/char-length.md): Alias for LENGTH. - [CHARACTER_LENGTH](https://docs.pingcap.com/tidbcloudlake/character-length.md): Alias for LENGTH. - [CONCAT](https://docs.pingcap.com/tidbcloudlake/concat.md): Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form. - [CONCAT_WS](https://docs.pingcap.com/tidbcloudlake/concat-ws.md): CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL. - [FROM_BASE64](https://docs.pingcap.com/tidbcloudlake/from-base64.md): Takes a string encoded with the base-64 encoded rules and returns the decoded result as a binary. The result is NULL if the argument is NULL or not a valid base-64 string. - [FROM_HEX](https://docs.pingcap.com/tidbcloudlake/from-hex.md): Alias for UNHEX. - [GLOB](https://docs.pingcap.com/tidbcloudlake/glob.md): Performs case-sensitive pattern matching using wildcard characters. - [HEX](https://docs.pingcap.com/tidbcloudlake/hex.md): Alias for TO_HEX. - [INSERT](https://docs.pingcap.com/tidbcloudlake/insert-sql.md): Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL. - [INSTR](https://docs.pingcap.com/tidbcloudlake/instr.md): Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed. - [JARO_WINKLER](https://docs.pingcap.com/tidbcloudlake/jaro-winkler.md): Calculates the Jaro-Winkler distance between two strings. It is commonly used for measuring the similarity between strings, with values ranging from 0.0 (completely dissimilar) to 1.0 (identical strings). - [LCASE](https://docs.pingcap.com/tidbcloudlake/lcase.md): Alias for LOWER. - [LEFT](https://docs.pingcap.com/tidbcloudlake/left.md): Returns the leftmost len characters from the string str, or NULL if any argument is NULL. If len is greater than the length of str, the entire str is returned. - [LENGTH](https://docs.pingcap.com/tidbcloudlake/length.md): Returns the length of a given input string or binary value. In the case of strings, the length represents the count of characters, with each UTF-8 character considered as a single character. For binary data, the length corresponds to the number of bytes. - [LENGTH_UTF8](https://docs.pingcap.com/tidbcloudlake/length-utf8.md): Alias for LENGTH. - [LIKE](https://docs.pingcap.com/tidbcloudlake/like.md): Pattern matching using an SQL pattern. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL. - [LOCATE](https://docs.pingcap.com/tidbcloudlake/locate.md): The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. Returns NULL if any argument is NULL. - [LOWER](https://docs.pingcap.com/tidbcloudlake/lower.md): Returns a string with all characters changed to lowercase. - [LPAD](https://docs.pingcap.com/tidbcloudlake/lpad.md): Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters. - [LTRIM](https://docs.pingcap.com/tidbcloudlake/ltrim.md): Removes all occurrences of any character present in the specified trim string from the left side of the string. - [MID](https://docs.pingcap.com/tidbcloudlake/mid.md): Alias for SUBSTR. - [NOT LIKE](https://docs.pingcap.com/tidbcloudlake/not-like.md): Pattern not matching using an SQL pattern. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL. - [NOT REGEXP](https://docs.pingcap.com/tidbcloudlake/not-regexp.md): Returns 1 if the string expr doesn't match the regular expression specified by the pattern pat, 0 otherwise. - [NOT RLIKE](https://docs.pingcap.com/tidbcloudlake/not-rlike.md): Returns 1 if the string expr doesn't match the regular expression specified by the pattern pat, 0 otherwise. - [OCT](https://docs.pingcap.com/tidbcloudlake/oct.md): Returns a string representation of the octal value of N. - [OCTET_LENGTH](https://docs.pingcap.com/tidbcloudlake/octet-length.md): OCTET_LENGTH() is a synonym for LENGTH(). - [ORD](https://docs.pingcap.com/tidbcloudlake/ord.md): If the leftmost character is not a multibyte character, ORD() returns the same value as the ASCII() function. - [POSITION](https://docs.pingcap.com/tidbcloudlake/position.md): POSITION(substr IN str) is a synonym for LOCATE(substr,str). Returns the position of the first occurrence of substring substr in string str. Returns 0 if substr is not in str. Returns NULL if any argument is NULL. - [QUOTE](https://docs.pingcap.com/tidbcloudlake/quote.md): Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. - [REGEXP](https://docs.pingcap.com/tidbcloudlake/regexp.md): Returns true if the string matches the regular expression specified by the , false otherwise. - [REGEXP_INSTR](https://docs.pingcap.com/tidbcloudlake/regexp-instr.md): Returns the starting index of the substring of the string expr that matches the regular expression specified by the pattern pat, 0 if there is no match. If expr or pat is NULL, the return value is NULL. Character indexes begin at 1. - [REGEXP_LIKE](https://docs.pingcap.com/tidbcloudlake/regexp-like.md): REGEXP_LIKE function is used to check that whether the string matches regular expression. - [REGEXP_REPLACE](https://docs.pingcap.com/tidbcloudlake/regexp-replace.md): Replaces occurrences in the string expr that match the regular expression specified by the pattern pat with the replacement string repl, and returns the resulting string. If expr, pat, or repl is NULL, the return value is NULL. - [REGEXP_SPLIT_TO_ARRAY](https://docs.pingcap.com/tidbcloudlake/regexp-split-array.md): Splits a string using a regular expression pattern and returns the segments as an array. - [REGEXP_SPLIT_TO_TABLE](https://docs.pingcap.com/tidbcloudlake/regexp-split-table.md): Splits a string using a regular expression pattern and returns each segment as a table. - [REGEXP_SUBSTR](https://docs.pingcap.com/tidbcloudlake/regexp-substr.md): Returns the substring of the string expr that matches the regular expression specified by the pattern pat, NULL if there is no match. If expr or pat is NULL, the return value is NULL. - [REPEAT](https://docs.pingcap.com/tidbcloudlake/repeat.md): Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL. - [REPLACE](https://docs.pingcap.com/tidbcloudlake/replace-sql.md): Returns the string str with all occurrences of the string from_str replaced by the string to_str. - [REVERSE](https://docs.pingcap.com/tidbcloudlake/reverse.md): Returns the string str with the order of the characters reversed. - [RIGHT](https://docs.pingcap.com/tidbcloudlake/right.md): Returns the rightmost len characters from the string str, or NULL if any argument is NULL. - [RLIKE](https://docs.pingcap.com/tidbcloudlake/rlike.md): Alias for REGEXP. - [RPAD](https://docs.pingcap.com/tidbcloudlake/rpad.md): Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters. - [RTRIM](https://docs.pingcap.com/tidbcloudlake/rtrim.md): Removes all occurrences of any character present in the specified trim string from the right side of the string. - [SOUNDEX](https://docs.pingcap.com/tidbcloudlake/soundex.md): Generates the Soundex code for a string. - [SOUNDS LIKE](https://docs.pingcap.com/tidbcloudlake/sounds-like.md): Compares the pronunciation of two strings by their Soundex codes. Soundex is a phonetic algorithm that produces a code representing the pronunciation of a string, allowing for approximate matching of strings based on their pronunciation rather than their spelling. TiDB Cloud Lake offers the SOUNDEX function that allows you to get the Soundex code from a string. - [SPACE](https://docs.pingcap.com/tidbcloudlake/space.md): Returns a string consisting of N blank space characters. - [SPLIT](https://docs.pingcap.com/tidbcloudlake/split.md): Splits a string using a specified delimiter and returns the resulting parts as an array. - [SPLIT_PART](https://docs.pingcap.com/tidbcloudlake/split-part.md): Splits a string using a specified delimiter and returns the specified part. - [STRCMP](https://docs.pingcap.com/tidbcloudlake/strcmp.md): Returns 0 if the strings are the same, -1 if the first argument is smaller than the second, and 1 otherwise. - [SUBSTR](https://docs.pingcap.com/tidbcloudlake/substr.md): Extracts a string containing a specific number of characters from a particular position of a given string. - [SUBSTRING](https://docs.pingcap.com/tidbcloudlake/substring.md): Alias for SUBSTR. - [TO_BASE64](https://docs.pingcap.com/tidbcloudlake/to-base64.md): Converts the string argument to base-64 encoded form and returns the result as a character string. If the argument is not a string, it is converted to a string before conversion takes place. The result is NULL if the argument is NULL. - [TRANSLATE](https://docs.pingcap.com/tidbcloudlake/translate.md): Transforms a given string by replacing specific characters with corresponding replacements, as defined by the provided mapping. - [TRIM](https://docs.pingcap.com/tidbcloudlake/trim.md): Removes spaces, specific characters, or substrings from the start, end, or both sides of a string. - [TRIM_BOTH](https://docs.pingcap.com/tidbcloudlake/trim-both.md): Removes all occurrences of the specified trim string from the beginning, end, or both sides of the string. - [TRIM_LEADING](https://docs.pingcap.com/tidbcloudlake/trim-leading.md): Removes all occurrences of the specified trim string from the beginning of the string. - [TRIM_TRAILING](https://docs.pingcap.com/tidbcloudlake/trim-trailing.md): Removes all occurrences of the specified trim string from the end of the string. - [UCASE](https://docs.pingcap.com/tidbcloudlake/ucase.md): Alias for UPPER. - [UNHEX](https://docs.pingcap.com/tidbcloudlake/unhex.md): For a string argument str, UNHEX(str) interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number. The return value is a binary string. - [UPPER](https://docs.pingcap.com/tidbcloudlake/upper.md): Returns a string with all characters changed to uppercase. - Aggregate Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/aggregate-functions.md): This page provides a comprehensive overview of aggregate functions in TiDB Cloud Lake, organized by functionality for easy reference. - [ANY_VALUE](https://docs.pingcap.com/tidbcloudlake/any-value.md): Aggregate function. - [APPROX_COUNT_DISTINCT](https://docs.pingcap.com/tidbcloudlake/approx-count-distinct.md): Estimates the number of distinct values in a data set with the HyperLogLog algorithm. - [ARG_MAX](https://docs.pingcap.com/tidbcloudlake/arg-max.md): Calculates the arg value for a maximum val value. If there are several values of arg for maximum values of val, returns the first of these values encountered. - [ARG_MIN](https://docs.pingcap.com/tidbcloudlake/arg-min.md): Calculates the arg value for a minimum val value. If there are several different values of arg for minimum values of val, returns the first of these values encountered. - [ARRAY_AGG](https://docs.pingcap.com/tidbcloudlake/array-agg.md): The ARRAY_AGG function (also known by its alias LIST) transforms all the values, excluding NULL, of a specific column in a query result into an array. - [AVG](https://docs.pingcap.com/tidbcloudlake/avg.md): Aggregate function. - [AVG_IF](https://docs.pingcap.com/tidbcloudlake/avg-if.md): The suffix -If can be appended to the name of any aggregate function. In this case, the aggregate function accepts an extra argument – a condition. - [bool_and](https://docs.pingcap.com/tidbcloudlake/bool-and.md): Returns true if all input values are true, otherwise false. - [bool_or](https://docs.pingcap.com/tidbcloudlake/bool-or.md): Returns true if at least one input value is true, otherwise false. - [COUNT](https://docs.pingcap.com/tidbcloudlake/count.md): The COUNT() function returns the number of records returned by a SELECT query. - [COUNT_DISTINCT](https://docs.pingcap.com/tidbcloudlake/count-distinct.md): Aggregate function. - [COUNT_IF](https://docs.pingcap.com/tidbcloudlake/count-if.md): The suffix _IF can be appended to the name of any aggregate function. In this case, the aggregate function accepts an extra argument – a condition. - [COVAR_POP](https://docs.pingcap.com/tidbcloudlake/covar-pop.md): Returns the population covariance of a set of number pairs. - [COVAR_SAMP](https://docs.pingcap.com/tidbcloudlake/covar-samp.md): Returns the sample covariance (Σ((x - x̅)(y - y̅)) / (n - 1)) of two data columns. - [GROUP_ARRAY_MOVING_AVG](https://docs.pingcap.com/tidbcloudlake/group-array-moving-avg.md): The GROUP_ARRAY_MOVING_AVG function calculates the moving average of input values. The function can take the window size as a parameter. If left unspecified, the function takes the window size equal to the number of input values. - [GROUP_ARRAY_MOVING_SUM](https://docs.pingcap.com/tidbcloudlake/group-array-moving-sum.md): The GROUP_ARRAY_MOVING_SUM function calculates the moving sum of input values. The function can take the window size as a parameter. If left unspecified, the function takes the window size equal to the number of input values. - [GROUP_CONCAT](https://docs.pingcap.com/tidbcloudlake/group-concat.md): Alias for LISTAGG. - [HISTOGRAM](https://docs.pingcap.com/tidbcloudlake/histogram.md): Generates a data distribution histogram using an "equal height" bucketing strategy. - [JSON_ARRAY_AGG](https://docs.pingcap.com/tidbcloudlake/json-array-agg.md): Converts values into a JSON array while skipping NULLs. - [JSON_OBJECT_AGG](https://docs.pingcap.com/tidbcloudlake/json-object-agg.md): Converts key-value pairs into a JSON object. For each row in the input, it generates a key-value pair where the key is derived from the and the value is derived from the . These key-value pairs are then combined into a single JSON object. - [KURTOSIS](https://docs.pingcap.com/tidbcloudlake/kurtosis.md): Aggregate function. - [LISTAGG](https://docs.pingcap.com/tidbcloudlake/listagg.md): Concatenates values from multiple rows into a single string, separated by a specified delimiter. This operation can be performed using two different function types - Aggregate Function The concatenation happens across all rows in the entire result set. - Window Function The concatenation happens within each partition of the result set, as defined by the PARTITION BY clause. - [MARKOV_TRAIN](https://docs.pingcap.com/tidbcloudlake/markov-train.md): Extracting patterns from datasets using Markov models. - [MAX](https://docs.pingcap.com/tidbcloudlake/max.md): Aggregate function. - [MAX_IF](https://docs.pingcap.com/tidbcloudlake/max-if.md): The suffix _IF can be appended to the name of any aggregate function. In this case, the aggregate function accepts an extra argument – a condition. - [MEDIAN](https://docs.pingcap.com/tidbcloudlake/median.md): The MEDIAN() function. - [MEDIAN_TDIGEST](https://docs.pingcap.com/tidbcloudlake/median-tdigest.md): Computes the median of a numeric data sequence using the t-digest algorithm. - [MIN](https://docs.pingcap.com/tidbcloudlake/min.md): Aggregate function. - [MIN_IF](https://docs.pingcap.com/tidbcloudlake/min-if.md): The suffix _IF can be appended to the name of any aggregate function. In this case, the aggregate function accepts an extra argument – a condition. - [MODE](https://docs.pingcap.com/tidbcloudlake/mode.md): Returns the value that appears most frequently in a group of values. - [QUANTILE_CONT](https://docs.pingcap.com/tidbcloudlake/quantile-cont.md): The QUANTILE_CONT() function computes the interpolated quantile number of a numeric data sequence. - [QUANTILE_DISC](https://docs.pingcap.com/tidbcloudlake/quantile-disc.md): The QUANTILE_DISC() function computes the exact quantile number of a numeric data sequence. - [QUANTILE_TDIGEST](https://docs.pingcap.com/tidbcloudlake/quantile-tdigest.md): Computes an approximate quantile of a numeric data sequence using the t-digest algorithm. - [QUANTILE_TDIGEST_WEIGHTED](https://docs.pingcap.com/tidbcloudlake/quantile-tdigest-weighted.md): Computes an approximate quantile of a numeric data sequence using the t-digest algorithm. This function takes into account the weight of each sequence member. Memory consumption is log(n), where n is a number of values. - [RETENTION](https://docs.pingcap.com/tidbcloudlake/retention.md): Aggregate function. - [SKEWNESS](https://docs.pingcap.com/tidbcloudlake/skewness.md): Aggregate function. - [ST_COLLECT](https://docs.pingcap.com/tidbcloudlake/st-collect.md): Collects multiple GEOMETRY values into a single GEOMETRY result. - [ST_ENVELOPE_AGG](https://docs.pingcap.com/tidbcloudlake/st-envelope-agg.md): Aggregates multiple GEOMETRY values and returns the minimum bounding rectangle that covers all non-NULL inputs. - [ST_INTERSECTION_AGG](https://docs.pingcap.com/tidbcloudlake/st-intersection-agg.md): Aggregates multiple GEOMETRY values by repeatedly applying ST_INTERSECTION and returns the common overlapping part. - [ST_UNION_AGG](https://docs.pingcap.com/tidbcloudlake/st-union-agg.md): Aggregates multiple GEOMETRY values by repeatedly applying ST_UNION and returns the merged GEOMETRY result. - [STDDEV_POP](https://docs.pingcap.com/tidbcloudlake/stddev-pop.md): Aggregate function. - [STDDEV_SAMP](https://docs.pingcap.com/tidbcloudlake/stddev-samp.md): Returns the sample standard deviation (the square root of VAR_SAMP()) of an expression. - [STRING_AGG](https://docs.pingcap.com/tidbcloudlake/string-agg.md): Alias for LISTAGG. - [SUM](https://docs.pingcap.com/tidbcloudlake/sum.md): Calculates the sum of a set of values. - [SUM_IF](https://docs.pingcap.com/tidbcloudlake/sum-if.md): The suffix -If can be appended to the name of any aggregate function. In this case, the aggregate function accepts an extra argument – a condition. - [VAR_POP](https://docs.pingcap.com/tidbcloudlake/var-pop.md): Alias for COVAR_POP. - [VAR_SAMP](https://docs.pingcap.com/tidbcloudlake/var-samp.md): Alias for COVAR_SAMP. - [VARIANCE_POP](https://docs.pingcap.com/tidbcloudlake/variance-pop.md): Alias for COVAR_POP. - [VARIANCE_SAMP](https://docs.pingcap.com/tidbcloudlake/variance-samp.md): Alias for COVAR_SAMP. - [WINDOW_FUNNEL](https://docs.pingcap.com/tidbcloudlake/window-funnel.md): Funnel Analysis. - Window Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/window-functions-overview.md): Window functions perform calculations across a set of related rows while returning one result per input row. Unlike aggregate functions, window functions don't collapse rows into a single output. - [CUME_DIST](https://docs.pingcap.com/tidbcloudlake/cume-dist.md): Calculates the cumulative distribution of each row's value. Returns the fraction of rows with values less than or equal to the current row's value. - [DENSE_RANK](https://docs.pingcap.com/tidbcloudlake/dense-rank.md): Assigns a rank to each row within a partition. Rows with equal values receive the same rank, with no gaps in subsequent rankings. - [FIRST](https://docs.pingcap.com/tidbcloudlake/first.md): Alias for FIRST_VALUE. - [FIRST_VALUE](https://docs.pingcap.com/tidbcloudlake/first-value.md): Returns the first value in the window frame. - [LAG](https://docs.pingcap.com/tidbcloudlake/lag.md): Returns the value from a previous row in the result set. - [LAST](https://docs.pingcap.com/tidbcloudlake/last.md): Alias for LAST_VALUE. - [LAST_VALUE](https://docs.pingcap.com/tidbcloudlake/last-value.md): Returns the last value in the window frame. - [LEAD](https://docs.pingcap.com/tidbcloudlake/lead.md): Returns the value from a subsequent row in the result set. - [NTH_VALUE](https://docs.pingcap.com/tidbcloudlake/nth-value.md): Returns the value at the specified position (N) within the window frame. - [NTILE](https://docs.pingcap.com/tidbcloudlake/ntile.md): Divides rows into a specified number of buckets and assigns a bucket number to each row. Rows are distributed as evenly as possible across buckets. - [PERCENT_RANK](https://docs.pingcap.com/tidbcloudlake/percent-rank.md): Calculates the relative rank of each row as a percentage. Returns values between 0 and 1, where 0 represents the lowest rank and 1 represents the highest rank. - [RANGE BETWEEN](https://docs.pingcap.com/tidbcloudlake/range-between.md): Defines a window frame using value-based boundaries for window functions. - [RANK](https://docs.pingcap.com/tidbcloudlake/rank.md): Assigns a rank to each row within a partition. Rows with equal values receive the same rank, with gaps in subsequent rankings. - [ROW_NUMBER](https://docs.pingcap.com/tidbcloudlake/row-number.md): Assigns a sequential number to each row within a partition, starting from 1. - [ROWS BETWEEN](https://docs.pingcap.com/tidbcloudlake/rows-between.md): Defines a window frame using row-based boundaries for window functions. - Geospatial Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/geospatial-functions.md): TiDB Cloud Lake ships with two complementary sets of geospatial capabilities PostGIS-style geometry functions for building and analysing shapes, and H3 utilities for global hexagonal indexing. The tables below group the functions by task so you can quickly locate the right tool, similar to the layout used in the Snowflake documentation. - [GEO_DISTANCE](https://docs.pingcap.com/tidbcloudlake/geo-distance.md): Returns the approximate distance in meters between two points on Earth. - [GEO_TO_H3](https://docs.pingcap.com/tidbcloudlake/geo-to-h3.md): Returns the H3 index of the hexagon cell where the given location resides. Returning 0 means an error occurred. - [GEOHASH_DECODE](https://docs.pingcap.com/tidbcloudlake/geohash-decode.md): Converts a Geohash-encoded string into latitude/longitude coordinates. - [GEOHASH_ENCODE](https://docs.pingcap.com/tidbcloudlake/geohash-encode.md): Converts a pair of latitude and longitude coordinates into a Geohash-encoded string. - [GREAT_CIRCLE_ANGLE](https://docs.pingcap.com/tidbcloudlake/great-circle-angle.md): Returns the central angle in degrees between two points on a sphere. - [GREAT_CIRCLE_DISTANCE](https://docs.pingcap.com/tidbcloudlake/great-circle-distance.md): Returns the great-circle distance in meters between two points on a sphere. - [H3_CELL_AREA_M2](https://docs.pingcap.com/tidbcloudlake/h3-cell-area-m2.md): Returns the exact area of specific cell in square meters. - [H3_CELL_AREA_RADS2](https://docs.pingcap.com/tidbcloudlake/h3-cell-area-rads2.md): Returns the exact area of specific cell in square radians. - [H3_DISTANCE](https://docs.pingcap.com/tidbcloudlake/h3-distance.md): Returns the grid distance between the the given two H3 indexes. - [H3_EDGE_ANGLE](https://docs.pingcap.com/tidbcloudlake/h3-edge-angle.md): Returns the average length of the H3 hexagon edge in grades. - [H3_EDGE_LENGTH_KM](https://docs.pingcap.com/tidbcloudlake/h3-edge-length-km.md): Returns the average hexagon edge length in kilometers at the given resolution. Excludes pentagons. - [H3_EDGE_LENGTH_M](https://docs.pingcap.com/tidbcloudlake/h3-edge-length-m.md): Returns the average hexagon edge length in meters at the given resolution. Excludes pentagons. - [H3_EXACT_EDGE_LENGTH_KM](https://docs.pingcap.com/tidbcloudlake/h3-exact-edge-length-km.md): Computes the length of this directed edge, in kilometers. - [H3_EXACT_EDGE_LENGTH_M](https://docs.pingcap.com/tidbcloudlake/h3-exact-edge-length-m.md): Computes the length of this directed edge, in meters. - [H3_EXACT_EDGE_LENGTH_RADS](https://docs.pingcap.com/tidbcloudlake/h3-exact-edge-length-rads.md): Computes the length of this directed edge, in radians. - [H3_GET_BASE_CELL](https://docs.pingcap.com/tidbcloudlake/h3-get-base-cell.md): Returns the base cell number of the given H3 index. - [H3_GET_DESTINATION_INDEX_FROM_UNIDIRECTIONAL_EDGE](https://docs.pingcap.com/tidbcloudlake/h3-get-destination-index-unidirectional-edge.md): Returns the destination hexagon index from the unidirectional edge H3Index. - [H3_GET_FACES](https://docs.pingcap.com/tidbcloudlake/h3-get-faces.md): Finds all icosahedron faces intersected by the given H3 index. Faces are represented as integers from 0-19. - [H3_GET_INDEXES_FROM_UNIDIRECTIONAL_EDGE](https://docs.pingcap.com/tidbcloudlake/h3-get-indexes-unidirectional-edge.md): Returns the origin and destination hexagon indexes from the given unidirectional edge H3Index. - [H3_GET_ORIGIN_INDEX_FROM_UNIDIRECTIONAL_EDGE](https://docs.pingcap.com/tidbcloudlake/h3-get-origin-index-unidirectional-edge.md): Returns the origin hexagon index from the unidirectional edge H3Index. - [H3_GET_RESOLUTION](https://docs.pingcap.com/tidbcloudlake/h3-get-resolution.md): Returns the resolution of the given H3 index. - [H3_GET_UNIDIRECTIONAL_EDGE](https://docs.pingcap.com/tidbcloudlake/h3-get-unidirectional-edge.md): Returns the edge between the given two H3 indexes. - [H3_GET_UNIDIRECTIONAL_EDGE_BOUNDARY](https://docs.pingcap.com/tidbcloudlake/h3-get-unidirectional-edge-boundary.md): Returns the coordinates defining the unidirectional edge. - [H3_GET_UNIDIRECTIONAL_EDGES_FROM_HEXAGON](https://docs.pingcap.com/tidbcloudlake/h3-get-unidirectional-edges-hexagon.md): Returns all of the unidirectional edges from the provided H3Index. - [H3_HEX_AREA_KM2](https://docs.pingcap.com/tidbcloudlake/h3-hex-area-km2.md): Returns the average hexagon area in square kilometers at the given resolution. Excludes pentagons. - [H3_HEX_AREA_M2](https://docs.pingcap.com/tidbcloudlake/h3-hex-area-m2.md): Returns the average hexagon area in square meters at the given resolution. Excludes pentagons. - [H3_HEX_RING](https://docs.pingcap.com/tidbcloudlake/h3-hex-ring.md): Returns the "hollow" ring of hexagons at exactly grid distance k from the given H3 index. - [H3_INDEXES_ARE_NEIGHBORS](https://docs.pingcap.com/tidbcloudlake/h3-indexes-are-neighbors.md): Returns whether or not the provided H3 indexes are neighbors. - [H3_IS_PENTAGON](https://docs.pingcap.com/tidbcloudlake/h3-is-pentagon.md): Checks if the given H3 index represents a pentagonal cell. - [H3_IS_RES_CLASS_III](https://docs.pingcap.com/tidbcloudlake/h3-is-res-class-iii.md): Checks if the given H3 index has a resolution with Class III orientation. - [H3_IS_VALID](https://docs.pingcap.com/tidbcloudlake/h3-is-valid.md): Checks if the given H3 index is valid. - [H3_K_RING](https://docs.pingcap.com/tidbcloudlake/h3-k-ring.md): Returns an array containing the H3 indexes of the k-ring hexagons surrounding the input H3 index. Each element in this array is an H3 index. - [H3_LINE](https://docs.pingcap.com/tidbcloudlake/h3-line.md): Returns the line of indexes between the given two H3 indexes. - [H3_NUM_HEXAGONS](https://docs.pingcap.com/tidbcloudlake/h3-num-hexagons.md): Returns the number of unique H3 indexes at the given resolution. - [H3_TO_CENTER_CHILD](https://docs.pingcap.com/tidbcloudlake/h3-to-center-child.md): Returns the center child index at the specified resolution. - [H3_TO_CHILDREN](https://docs.pingcap.com/tidbcloudlake/h3-to-children.md): Returns the indexes contained by h3 at resolution child_res. - [H3_TO_GEO](https://docs.pingcap.com/tidbcloudlake/h3-to-geo.md): Returns the longitude and latitude corresponding to the given H3 index. - [H3_TO_GEO_BOUNDARY](https://docs.pingcap.com/tidbcloudlake/h3-to-geo-boundary.md): Returns an array containing the longitude and latitude coordinates of the vertices of the hexagon corresponding to the H3 index. - [H3_TO_PARENT](https://docs.pingcap.com/tidbcloudlake/h3-to-parent.md): Returns the parent index containing the h3 at resolution parent_res. Returning 0 means an error occurred. - [H3_TO_STRING](https://docs.pingcap.com/tidbcloudlake/h3-to-string.md): Converts the representation of the given H3 index to the string representation. - [H3_UNIDIRECTIONAL_EDGE_IS_VALID](https://docs.pingcap.com/tidbcloudlake/h3-unidirectional-edge-is-valid.md): Determines if the provided H3Index is a valid unidirectional edge index. Returns 1 if it's a unidirectional edge and 0 otherwise. - [HAVERSINE](https://docs.pingcap.com/tidbcloudlake/haversine.md): Calculates the great circle distance in kilometers between two points on the Earth’s surface, using the Haversine formula. The two points are specified by their latitude and longitude in degrees. - [POINT_IN_ELLIPSES](https://docs.pingcap.com/tidbcloudlake/point-in-ellipses.md): Returns 1 if the point lies inside any of the provided ellipses, otherwise returns 0. - [POINT_IN_POLYGON](https://docs.pingcap.com/tidbcloudlake/point-in-polygon.md): Calculates whether a given point falls within the polygon formed by joining multiple points. A polygon is a closed shape connected by coordinate pairs in the order they appear. Changing the order of coordinate pairs can result in a different shape. - [ST_AREA](https://docs.pingcap.com/tidbcloudlake/st-area.md): Returns the area of a GEOMETRY or GEOGRAPHY object. For GEOMETRY inputs, the function uses planar area based on the shoelace formula. For GEOGRAPHY inputs, the function measures geodesic area on an ellipsoidal model of the earth using the method described in Karney (2013). - [ST_ASBINARY](https://docs.pingcap.com/tidbcloudlake/st-asbinary.md): Alias for ST_ASWKB. - [ST_ASEWKB](https://docs.pingcap.com/tidbcloudlake/st-asewkb.md): Converts a GEOMETRY or GEOGRAPHY object into a EWKB(extended well-known-binary) format representation. - [ST_ASEWKT](https://docs.pingcap.com/tidbcloudlake/st-asewkt.md): Converts a GEOMETRY or GEOGRAPHY object into a EWKT(extended well-known-text) format representation. - [ST_ASGEOJSON](https://docs.pingcap.com/tidbcloudlake/st-asgeojson.md): Converts a GEOMETRY or GEOGRAPHY object into a GeoJSON representation. - [ST_ASTEXT](https://docs.pingcap.com/tidbcloudlake/st-astext.md): Alias for ST_ASWKT. - [ST_ASWKB](https://docs.pingcap.com/tidbcloudlake/st-aswkb.md): Converts a GEOMETRY or GEOGRAPHY object into a WKB(well-known-binary) format representation. - [ST_ASWKT](https://docs.pingcap.com/tidbcloudlake/st-aswkt.md): Converts a GEOMETRY or GEOGRAPHY object into a WKT(well-known-text) format representation. - [ST_AZIMUTH](https://docs.pingcap.com/tidbcloudlake/st-azimuth.md): Returns the azimuth in radians of the line segment from one Point to another, measured clockwise from the positive Y-axis (north). Returns NULL if the two points are identical. - [ST_BUFFER](https://docs.pingcap.com/tidbcloudlake/st-buffer.md): Returns a GEOMETRY representing all points whose distance from the input geometry is less than or equal to the specified distance. The result is a MultiPolygon or NULL. - [ST_CENTROID](https://docs.pingcap.com/tidbcloudlake/st-centroid.md): Returns the centroid of a GEOMETRY object. - [ST_CONTAINS](https://docs.pingcap.com/tidbcloudlake/st-contains.md): Returns TRUE if the second GEOMETRY object is completely inside the first GEOMETRY object. - [ST_CONVEXHULL](https://docs.pingcap.com/tidbcloudlake/st-convexhull.md): Returns the convex hull of a GEOMETRY object. - [ST_COVEREDBY](https://docs.pingcap.com/tidbcloudlake/st-coveredby.md): Returns TRUE if no point in the first GEOMETRY object lies outside the second GEOMETRY object. - [ST_COVERS](https://docs.pingcap.com/tidbcloudlake/st-covers.md): Returns TRUE if no point in the second GEOMETRY object lies outside the first GEOMETRY object. - [ST_DIFFERENCE](https://docs.pingcap.com/tidbcloudlake/st-difference.md): Returns the part of the first GEOMETRY object that is not covered by the second GEOMETRY object. - [ST_DIMENSION](https://docs.pingcap.com/tidbcloudlake/st-dimension.md): Return the dimension for a geometry object. The dimension of a GEOMETRY or GEOGRAPHY object is. - [ST_DISJOINT](https://docs.pingcap.com/tidbcloudlake/st-disjoint.md): Returns TRUE if two GEOMETRY objects do not intersect. - [ST_DISTANCE](https://docs.pingcap.com/tidbcloudlake/st-distance.md): Returns the minimum distance between two objects. For GEOMETRY inputs, the function uses Euclidean distance. For GEOGRAPHY inputs, the function uses haversine distance. - [ST_DWITHIN](https://docs.pingcap.com/tidbcloudlake/st-dwithin.md): Returns TRUE if two GEOMETRY objects are within the specified Euclidean distance. - [ST_ENDPOINT](https://docs.pingcap.com/tidbcloudlake/st-endpoint.md): Returns the last Point in a LineString. - [ST_ENVELOPE](https://docs.pingcap.com/tidbcloudlake/st-envelope.md): Returns the minimum bounding rectangle of a GEOMETRY object as a polygon. - [ST_EQUALS](https://docs.pingcap.com/tidbcloudlake/st-equals.md): Returns TRUE if two GEOMETRY objects are spatially equal. - [ST_GEOGETRYFROMWKB](https://docs.pingcap.com/tidbcloudlake/st-geogetryfromwkb.md): Alias for ST_GEOGRAPHYFROMWKB. - [ST_GEOGFROMEWKB](https://docs.pingcap.com/tidbcloudlake/st-geogfromewkb.md): Alias for ST_GEOGRAPHYFROMWKB. - [ST_GEOGFROMGEOHASH](https://docs.pingcap.com/tidbcloudlake/st-geogfromgeohash.md): Returns a GEOGRAPHY object for the polygon that represents the boundaries of a geohash. - [ST_GEOGFROMTEXT](https://docs.pingcap.com/tidbcloudlake/st-geogfromtext.md): Alias for ST_GEOGRAPHYFROMWKT. - [ST_GEOGFROMWKB](https://docs.pingcap.com/tidbcloudlake/st-geogfromwkb.md): Alias for ST_GEOGRAPHYFROMWKB. - [ST_GEOGFROMWKT](https://docs.pingcap.com/tidbcloudlake/st-geogfromwkt.md): Alias for ST_GEOGRAPHYFROMWKT. - [ST_GEOGPOINTFROMGEOHASH](https://docs.pingcap.com/tidbcloudlake/st-geogpointfromgeohash.md): Returns a GEOGRAPHY object for the point that represents center of a geohash. - [ST_GEOGRAPHYFROMEWKT](https://docs.pingcap.com/tidbcloudlake/st-geographyfromewkt.md): Alias for ST_GEOGRAPHYFROMWKT. - [ST_GEOGRAPHYFROMTEXT](https://docs.pingcap.com/tidbcloudlake/st-geographyfromtext.md): Alias for ST_GEOGRAPHYFROMWKT. - [ST_GEOGRAPHYFROMWKB](https://docs.pingcap.com/tidbcloudlake/st-geographyfromwkb.md): Parses a WKB(well-known-binary) or EWKB(extended well-known-binary) input and returns a value of type GEOGRAPHY. - [ST_GEOGRAPHYFROMWKT](https://docs.pingcap.com/tidbcloudlake/st-geographyfromwkt.md): Parses a WKT(well-known-text) or EWKT(extended well-known-text) input and returns a value of type GEOGRAPHY. - [ST_GEOHASH](https://docs.pingcap.com/tidbcloudlake/st-geohash.md): Returns the geohash string for a GEOMETRY or GEOGRAPHY value and supports optional precision to control result granularity. - [ST_GEOM_POINT](https://docs.pingcap.com/tidbcloudlake/st-geom-point.md): Alias for ST_MAKEGEOMPOINT. - [ST_GEOMETRYFROMEWKB](https://docs.pingcap.com/tidbcloudlake/st-geometryfromewkb.md): Alias for ST_GEOMTRYFROMWKB. - [ST_GEOMETRYFROMEWKT](https://docs.pingcap.com/tidbcloudlake/st-geometryfromewkt.md): Alias for ST_GEOMTRYFROMWKT. - [ST_GEOMETRYFROMTEXT](https://docs.pingcap.com/tidbcloudlake/st-geometryfromtext.md): Alias for ST_GEOMETRYFROMWKT. - [ST_GEOMETRYFROMWKB](https://docs.pingcap.com/tidbcloudlake/st-geometryfromwkb.md): Parses a WKB(well-known-binary) or EWKB(extended well-known-binary) input and returns a value of type GEOMETRY. - [ST_GEOMETRYFROMWKT](https://docs.pingcap.com/tidbcloudlake/st-geometryfromwkt.md): Parses a WKT(well-known-text) or EWKT(extended well-known-text) input and returns a value of type GEOMETRY. - [ST_GEOMFROMEWKB](https://docs.pingcap.com/tidbcloudlake/st-geomfromewkb.md): Alias for ST_GEOMTRYFROMWKB. - [ST_GEOMFROMEWKT](https://docs.pingcap.com/tidbcloudlake/st-geomfromewkt.md): Alias for ST_GEOMTRYFROMWKT. - [ST_GEOMFROMGEOHASH](https://docs.pingcap.com/tidbcloudlake/st-geomfromgeohash.md): Returns a GEOMETRY object for the polygon that represents the boundaries of a geohash. - [ST_GEOMFROMTEXT](https://docs.pingcap.com/tidbcloudlake/st-geomfromtext.md): Alias for ST_GEOMTRYFROMWKT. - [ST_GEOMFROMWKB](https://docs.pingcap.com/tidbcloudlake/st-geomfromwkb.md): Alias for ST_GEOMTRYFROMWKB. - [ST_GEOMFROMWKT](https://docs.pingcap.com/tidbcloudlake/st-geomfromwkt.md): Alias for ST_GEOMTRYFROMWKT. - [ST_GEOMPOINTFROMGEOHASH](https://docs.pingcap.com/tidbcloudlake/st-geompointfromgeohash.md): Returns a GEOMETRY object for the point that represents center of a geohash. - [ST_HAUSDORFFDISTANCE](https://docs.pingcap.com/tidbcloudlake/st-hausdorffdistance.md): Returns the discrete Hausdorff distance between two GEOMETRY objects. This measures how far apart two geometries are by finding the greatest distance from any vertex in one object to the nearest vertex in the other. - [ST_HILBERT](https://docs.pingcap.com/tidbcloudlake/st-hilbert.md): Encodes a GEOMETRY or GEOGRAPHY object into a Hilbert curve index. - [ST_INTERSECTION](https://docs.pingcap.com/tidbcloudlake/st-intersection.md): Returns the shared part of two GEOMETRY objects. - [ST_INTERSECTS](https://docs.pingcap.com/tidbcloudlake/st-intersects.md): Returns TRUE if two GEOMETRY objects share any portion of space. - [ST_ISVALID](https://docs.pingcap.com/tidbcloudlake/st-isvalid.md): Returns TRUE if the GEOMETRY object is geometrically valid as defined by the OGC specification. - [ST_LENGTH](https://docs.pingcap.com/tidbcloudlake/st-length.md): Returns the Euclidean length of the LineString(s) in a GEOMETRY or GEOGRAPHY object. - [ST_MAKE_LINE](https://docs.pingcap.com/tidbcloudlake/st-make-line.md): Alias for ST_MAKELINE. - [ST_MAKEGEOMPOINT](https://docs.pingcap.com/tidbcloudlake/st-makegeompoint.md): Constructs a GEOMETRY object that represents a Point with the specified longitude and latitude. - [ST_MAKELINE](https://docs.pingcap.com/tidbcloudlake/st-makeline.md): Constructs a GEOMETRY or GEOGRAPHY object that represents a line connecting the points in the input two GEOMETRY or GEOGRAPHY objects. - [ST_MAKEPOINT](https://docs.pingcap.com/tidbcloudlake/st-makepoint.md): Constructs a GEOGRAPHY object that represents a Point with the specified longitude and latitude. - [ST_MAKEPOLYGON](https://docs.pingcap.com/tidbcloudlake/st-makepolygon.md): Constructs a GEOMETRY or GEOGRAPHY object that represents a Polygon without holes. The function uses the specified LineString as the outer loop. - [ST_MAKEPOLYGONORIENTED](https://docs.pingcap.com/tidbcloudlake/st-makepolygonoriented.md): Creates a Polygon from a LineString input, preserving the vertex order as given. Unlike ST_MAKEPOLYGON, this function does not reorder vertices to enforce a specific winding direction. - [ST_NPOINTS](https://docs.pingcap.com/tidbcloudlake/st-npoints.md): Returns the number of points in a GEOMETRY or GEOGRAPHY object. - [ST_NUMPOINTS](https://docs.pingcap.com/tidbcloudlake/st-numpoints.md): Alias for ST_NPOINTS. - [ST_PERIMETER](https://docs.pingcap.com/tidbcloudlake/st-perimeter.md): Returns the perimeter of polygon(s) in a GEOMETRY object, measured in the units of the coordinate system. - [ST_POINT](https://docs.pingcap.com/tidbcloudlake/st-point.md): Alias for ST_MAKEPOINT. - [ST_POINTN](https://docs.pingcap.com/tidbcloudlake/st-pointn.md): Returns a Point at a specified index in a LineString. - [ST_POLYGON](https://docs.pingcap.com/tidbcloudlake/st-polygon.md): Alias for ST_MAKEPOLYGON. - [ST_SETSRID](https://docs.pingcap.com/tidbcloudlake/st-setsrid.md): Returns a GEOMETRY object that has its SRID (spatial reference system identifier) set to the specified value. This Function only change the SRID without affecting the coordinates of the object. If you also need to change the coordinates to match the new SRS (spatial reference system), use ST_TRANSFORM instead. - [ST_SIMPLIFY](https://docs.pingcap.com/tidbcloudlake/st-simplify.md): Returns a simplified version of a GEOMETRY object by removing vertices where the distance to the resulting edge is within the specified tolerance. Uses the Ramer-Douglas-Peucker algorithm. - [ST_SRID](https://docs.pingcap.com/tidbcloudlake/st-srid.md): Returns the SRID (spatial reference system identifier) of a GEOMETRY or GEOGRAPHY object. - [ST_STARTPOINT](https://docs.pingcap.com/tidbcloudlake/st-startpoint.md): Returns the first Point in a LineString. - [ST_SYMDIFFERENCE](https://docs.pingcap.com/tidbcloudlake/st-symdifference.md): Returns the non-overlapping parts of two GEOMETRY objects. - [ST_TRANSFORM](https://docs.pingcap.com/tidbcloudlake/st-transform.md): Converts a GEOMETRY object from one spatial reference system (SRS) to another. If you just need to change the SRID without changing the coordinates (e.g. if the SRID was incorrect), use ST_SETSRID instead. - [ST_UNION](https://docs.pingcap.com/tidbcloudlake/st-union.md): Returns the combined GEOMETRY made from two input GEOMETRY objects. - [ST_WITHIN](https://docs.pingcap.com/tidbcloudlake/st-within.md): Returns TRUE if the first GEOMETRY object is completely within the second GEOMETRY object. - [ST_X](https://docs.pingcap.com/tidbcloudlake/st-x.md): Returns the longitude (X coordinate) of a Point represented by a GEOMETRY or GEOGRAPHY object. - [ST_XMAX](https://docs.pingcap.com/tidbcloudlake/st-xmax.md): Returns the maximum longitude (X coordinate) of all points contained in the specified GEOMETRY or GEOGRAPHY object. - [ST_XMIN](https://docs.pingcap.com/tidbcloudlake/st-xmin.md): Returns the minimum longitude (X coordinate) of all points contained in the specified GEOMETRY or GEOGRAPHY object. - [ST_Y](https://docs.pingcap.com/tidbcloudlake/st-y.md): Returns the latitude (Y coordinate) of a Point represented by a GEOMETRY or GEOGRAPHY object. - [ST_YMAX](https://docs.pingcap.com/tidbcloudlake/st-ymax.md): Returns the maximum latitude (Y coordinate) of all points contained in the specified GEOMETRY or GEOGRAPHY object. - [ST_YMIN](https://docs.pingcap.com/tidbcloudlake/st-ymin.md): Returns the minimum latitude (Y coordinate) of all points contained in the specified GEOMETRY or GEOGRAPHY object. - [STRING_TO_H3](https://docs.pingcap.com/tidbcloudlake/string-to-h3.md): Converts the string representation to H3 (uint64) representation. - [TO_GEOGRAPHY](https://docs.pingcap.com/tidbcloudlake/to-geography.md): Parses an input and returns a value of type GEOGRAPHY. - [TO_GEOMETRY](https://docs.pingcap.com/tidbcloudlake/geometry.md): Parses an input and returns a value of type GEOMETRY. - [TO_STRING](https://docs.pingcap.com/tidbcloudlake/string-geospatial.md): Converts a GEOMETRY object into a String representation. The display format of the output data is controlled by the geometry_output_format setting, which contains the following types. - Full-Text Search Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/full-text-search-functions.md): TiDB Cloud Lake's full-text search functions deliver search-engine-style filtering for semi-structured VARIANT data and plain text columns that are indexed with an inverted index. They are ideal for AI-generated metadata—such as perception results from autonomous-driving video frames—stored alongside your assets. - [MATCH](https://docs.pingcap.com/tidbcloudlake/match.md): Searches indexed columns for keyword matches and can be used only in a WHERE clause. - [QUERY](https://docs.pingcap.com/tidbcloudlake/query.md): Filters rows with a Lucene-style query against columns that have an inverted index and supports dot notation for nested VARIANT fields. - [SCORE](https://docs.pingcap.com/tidbcloudlake/score.md): Returns the relevance score for rows matched by inverted-index search conditions. - Structured & Semi-Structured - [Structured & Semi-Structured Functions](https://docs.pingcap.com/tidbcloudlake/structured-semi-structured-functions.md): Structured and semi-structured functions in TiDB Cloud Lake enable efficient processing of arrays, objects, maps, JSON, and other structured data formats. These functions provide comprehensive capabilities for creating, parsing, querying, transforming, and manipulating structured and semi-structured data. - JSON Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/json-functions-overview.md): This section provides reference information for JSON functions in TiDB Cloud Lake. JSON functions enable parsing, validation, querying, and manipulation of JSON data structures. - [CHECK_JSON](https://docs.pingcap.com/tidbcloudlake/check-json.md): Checks the validity of a JSON document. If the input string is a valid JSON document or a NULL, the output is NULL. If the input cannot be translated to a valid JSON value, the output string contains the error message. - [GET](https://docs.pingcap.com/tidbcloudlake/get.md): Extracts value from a Variant that contains ARRAY by index, or a Variant that contains OBJECT by field_name. The value is returned as a Variant or NULL if either of the arguments is NULL. - [GET_BY_KEYPATH](https://docs.pingcap.com/tidbcloudlake/get-by-keypath.md): Extracts a nested value from a VARIANT using a key path string. GET_BY_KEYPATH returns the result as VARIANT, while GET_BY_KEYPATH_STRING returns a STRING. - [GET_IGNORE_CASE](https://docs.pingcap.com/tidbcloudlake/get-ignore-case.md): Extracts value from a VARIANT that contains OBJECT by the field_name. The value is returned as a Variant or NULL if either of the arguments is NULL. - [GET_PATH](https://docs.pingcap.com/tidbcloudlake/get-path.md): Extracts value from a VARIANT by path_name. The value is returned as a Variant or NULL if either of the arguments is NULL. - [JQ](https://docs.pingcap.com/tidbcloudlake/jq.md): The JQ function is a set-returning SQL function that allows you to apply jq filters to JSON data stored in Variant columns. With this function, you can process JSON data by applying a specified jq filter, returning the results as a set of rows. - [JSON_ARRAY_ELEMENTS](https://docs.pingcap.com/tidbcloudlake/json-array-elements.md): Extracts the elements from a JSON array, returning them as individual rows in the result set. JSON_ARRAY_ELEMENTS does not recursively expand nested arrays; it treats them as single elements. - [JSON_CONTAINS_IN_LEFT](https://docs.pingcap.com/tidbcloudlake/json-contains-left.md): Tests containment relationships between two VARIANT values. - [JSON_EACH](https://docs.pingcap.com/tidbcloudlake/json-each.md): Extracts key-value pairs from a JSON object, breaking down the structure into individual rows in the result set. Each row represents a distinct key-value pair derived from the input JSON expression. - [JSON_EXISTS_KEY](https://docs.pingcap.com/tidbcloudlake/json-exists-key.md): Checks whether a JSON object contains one or more keys. - [JSON_EXTRACT_PATH_TEXT](https://docs.pingcap.com/tidbcloudlake/json-extract-path-text.md): Extracts value from a Json string by path_name. The value is returned as a String or NULL if either of the arguments is NULL. This function is equivalent to to_varchar(GET_PATH(PARSE_JSON(JSON), PATH_NAME)). - [JSON_PATH_EXISTS](https://docs.pingcap.com/tidbcloudlake/json-path-exists.md): Checks whether a specified path exists in JSON data. - [JSON_PATH_MATCH](https://docs.pingcap.com/tidbcloudlake/json-path-match.md): Checks whether a specified JSON path expression matches certain conditions within a JSON data. Please note that the @@ operator is synonymous with this function. For more information, see JSON Operators. - [JSON_PATH_QUERY](https://docs.pingcap.com/tidbcloudlake/json-path-query.md): Get all JSON items returned by JSON path for the specified JSON value. - [JSON_PATH_QUERY_ARRAY](https://docs.pingcap.com/tidbcloudlake/json-path-query-array.md): Get all JSON items returned by JSON path for the specified JSON value and wrap a result into an array. - [JSON_PATH_QUERY_FIRST](https://docs.pingcap.com/tidbcloudlake/json-path-query-first.md): Get the first JSON item returned by JSON path for the specified JSON value. - [JSON_PRETTY](https://docs.pingcap.com/tidbcloudlake/json-pretty.md): Formats JSON data, making it more readable and presentable. It automatically adds indentation, line breaks, and other formatting to the JSON data for better visual representation. - [JSON_STRIP_NULLS](https://docs.pingcap.com/tidbcloudlake/json-strip-nulls.md): Removes all properties with null values from a JSON object. - [JSON_TO_STRING](https://docs.pingcap.com/tidbcloudlake/json-to-string.md): Alias for TO_STRING. - [JSON_TYPEOF](https://docs.pingcap.com/tidbcloudlake/json-typeof.md): Returns the type of the main-level of a JSON structure. - [PARSE_JSON](https://docs.pingcap.com/tidbcloudlake/parse-json.md): Parses a JSON-formatted string and returns a VARIANT value. Use TRY_PARSE_JSON to return NULL instead of an error for invalid input. - [STRIP_NULL_VALUE](https://docs.pingcap.com/tidbcloudlake/strip-null-value.md): Converts a JSON null value to a SQL NULL value. All other variant values are passed unchanged. - Array Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/array-functions.md): This section provides reference information for array functions in TiDB Cloud Lake. Array functions enable creation, manipulation, searching, and transformation of array data structures. - [ARRAY](https://docs.pingcap.com/tidbcloudlake/array-sql.md): Builds an array literal from the supplied expressions. Each argument is evaluated and stored in order. All elements must be castable to a common type. - [ARRAY_AGGREGATE](https://docs.pingcap.com/tidbcloudlake/array-aggregate.md): Aggregates elements in the array with an aggregate function. - [ARRAY_ANY](https://docs.pingcap.com/tidbcloudlake/array-any.md): Returns the first non-NULL element from an array. Equivalent to ARRAY_AGGREGATE(, 'ANY'). - [ARRAY_APPEND](https://docs.pingcap.com/tidbcloudlake/array-append.md): Appends an element to the end of an array. - [ARRAY_APPROX_COUNT_DISTINCT](https://docs.pingcap.com/tidbcloudlake/array-approx-count-distinct.md): Returns an approximate count of distinct elements in an array, ignoring NULL values. This uses the same HyperLogLog-based estimator as APPROX_COUNT_DISTINCT. - [ARRAY_AVG](https://docs.pingcap.com/tidbcloudlake/array-avg.md): Returns the average of the numeric items in an array. NULL elements are ignored; non-numeric values raise an error. - [ARRAY_COMPACT](https://docs.pingcap.com/tidbcloudlake/array-compact.md): Removes all NULL values from an array. - [ARRAY_CONCAT](https://docs.pingcap.com/tidbcloudlake/array-concat.md): Concats two arrays. - [ARRAY_CONSTRUCT](https://docs.pingcap.com/tidbcloudlake/array-construct.md): Creates a JSON array with specified values. - [ARRAY_CONTAINS](https://docs.pingcap.com/tidbcloudlake/array-contains.md): Returns true if the array contains the specified element. - [ARRAY_COUNT](https://docs.pingcap.com/tidbcloudlake/array-count.md): Counts the non-NULL elements in an array. - [ARRAY_DISTINCT](https://docs.pingcap.com/tidbcloudlake/array-distinct.md): Removes duplicate elements from a JSON array and returns an array with only distinct elements. - [ARRAY_EXCEPT](https://docs.pingcap.com/tidbcloudlake/array-except.md): Returns a new JSON array containing the elements from the first JSON array that are not present in the second JSON array. - [ARRAY_FILTER](https://docs.pingcap.com/tidbcloudlake/array-filter.md): Filters elements from a JSON array based on a specified Lambda expression, returning only the elements that satisfy the condition. For more information about Lambda expression, see Lambda Expressions. - [ARRAY_FLATTEN](https://docs.pingcap.com/tidbcloudlake/array-flatten.md): Flattens a nested array into a single-dimensional array. - [ARRAY_GENERATE_RANGE](https://docs.pingcap.com/tidbcloudlake/array-generate-range.md): Builds an array of evenly spaced integers between a start and end value. The end bound is exclusive. - [ARRAY_GET](https://docs.pingcap.com/tidbcloudlake/array-get.md): Alias for GET. - [ARRAY_INDEXOF](https://docs.pingcap.com/tidbcloudlake/array-indexof.md): Returns the index of the first occurrence of an element in an array. - [ARRAY_INSERT](https://docs.pingcap.com/tidbcloudlake/array-insert.md): Inserts a value into a JSON array at the specified index and returns the updated JSON array. - [ARRAY_INTERSECTION](https://docs.pingcap.com/tidbcloudlake/array-intersection.md): Returns the common elements between two JSON arrays. - [ARRAY_KURTOSIS](https://docs.pingcap.com/tidbcloudlake/array-kurtosis.md): Returns the excess kurtosis of the numeric values in an array. NULL elements are ignored; non-numeric elements raise an error. - [ARRAY_MAX](https://docs.pingcap.com/tidbcloudlake/array-max.md): Returns the largest numeric value in an array. NULL elements are skipped; non-numeric values cause an error. - [ARRAY_MEDIAN](https://docs.pingcap.com/tidbcloudlake/array-median.md): Returns the median of the numeric values in an array. NULL elements are ignored. - [ARRAY_MIN](https://docs.pingcap.com/tidbcloudlake/array-min.md): Returns the smallest numeric value in an array. NULL elements are skipped; non-numeric values cause an error. - [ARRAY_OVERLAP](https://docs.pingcap.com/tidbcloudlake/array-overlap.md): Checks if there is any overlap between two JSON arrays and returns true if there are common elements; otherwise, it returns false. - [ARRAY_PREPEND](https://docs.pingcap.com/tidbcloudlake/array-prepend.md): Prepends an element to the beginning of an array. - [ARRAY_REDUCE](https://docs.pingcap.com/tidbcloudlake/array-reduce.md): Reduces a JSON array to a single value by applying a specified Lambda expression. For more information about Lambda expression, see Lambda Expressions. - [ARRAY_REMOVE](https://docs.pingcap.com/tidbcloudlake/array-remove.md): Removes all occurrences of an element from an array. - [ARRAY_REMOVE_FIRST](https://docs.pingcap.com/tidbcloudlake/array-remove-first.md): Removes the first occurrence of an element from an array. - [ARRAY_REMOVE_LAST](https://docs.pingcap.com/tidbcloudlake/array-remove-last.md): Removes the last occurrence of an element from an array. - [ARRAY_REVERSE](https://docs.pingcap.com/tidbcloudlake/array-reverse.md): Reverses the order of elements in an array. - [ARRAY_SIZE](https://docs.pingcap.com/tidbcloudlake/array-size.md): Returns the length of an array, counting NULL elements. - [ARRAY_SKEWNESS](https://docs.pingcap.com/tidbcloudlake/array-skewness.md): Returns the skewness of numeric values in an array. NULL items are ignored; non-numeric items raise an error. - [ARRAY_SLICE](https://docs.pingcap.com/tidbcloudlake/array-slice.md): Extracts a sub-array using slice between start and end arguments. - [ARRAY_SORT](https://docs.pingcap.com/tidbcloudlake/array-sort.md): Sorts the elements of an array. By default, ARRAY_SORT orders ascending and places NULL values last. Use the explicit variants to control order and NULL placement. - [ARRAY_STDDEV_POP](https://docs.pingcap.com/tidbcloudlake/array-stddev-pop.md): Computes the population standard deviation of numeric array values. NULL entries are ignored; non-numeric entries raise an error. - [ARRAY_STDDEV_SAMP](https://docs.pingcap.com/tidbcloudlake/array-stddev-samp.md): Computes the sample standard deviation of numeric array values. NULL items are ignored; non-numeric entries raise an error. - [ARRAY_SUM](https://docs.pingcap.com/tidbcloudlake/array-sum.md): Sums the numeric elements in an array. NULL items are skipped, and non-numeric values raise an error. - [ARRAY_TO_STRING](https://docs.pingcap.com/tidbcloudlake/array-to-string.md): Concatenates the string elements of an array into a single string, separated by a delimiter. NULL elements are skipped. - [JSON_ARRAY_TRANSFORM](https://docs.pingcap.com/tidbcloudlake/json-array-transform.md): Transforms each element of a JSON array using a specified transformation Lambda expression. For more information about Lambda expression, see Lambda Expressions. - [ARRAY_UNIQUE](https://docs.pingcap.com/tidbcloudlake/array-unique.md): Returns the number of unique elements in the array. - [ARRAYS_ZIP](https://docs.pingcap.com/tidbcloudlake/arrays-zip.md): Merges multiple arrays into a single array tuple. - [CONTAINS](https://docs.pingcap.com/tidbcloudlake/contains.md): Checks if the array contains a specific element. - [GET](https://docs.pingcap.com/tidbcloudlake/get-sql.md): Returns an element from an array by index (1-based). - [RANGE](https://docs.pingcap.com/tidbcloudlake/range.md): Returns an array collected by [start, end). - [SLICE](https://docs.pingcap.com/tidbcloudlake/slice.md): Extracts a slice from the array by index (1-based). - [UNNEST](https://docs.pingcap.com/tidbcloudlake/unnest.md): Unnests the array and returns the set of elements. - Object Functions - [Object Functions](https://docs.pingcap.com/tidbcloudlake/object-functions.md): This section provides reference information for object functions in TiDB Cloud Lake. Object functions enable creation, manipulation, and extraction of information from JSON object data structures. - [OBJECT_CONSTRUCT](https://docs.pingcap.com/tidbcloudlake/object-construct.md): Creates a JSON object with keys and values. - [OBJECT_CONSTRUCT_KEEP_NULL](https://docs.pingcap.com/tidbcloudlake/object-construct-keep-null.md): Creates a JSON object with keys and values. - [OBJECT_DELETE](https://docs.pingcap.com/tidbcloudlake/object-delete.md): Deletes specified keys from a JSON object and returns the modified object. If a specified key doesn't exist in the object, it is ignored. - [OBJECT_INSERT](https://docs.pingcap.com/tidbcloudlake/object-insert.md): Inserts or updates a key-value pair in a JSON object. - [OBJECT_KEYS](https://docs.pingcap.com/tidbcloudlake/object-keys.md): Returns the keys of the outermost JSON object as an array of strings. - [OBJECT_PICK](https://docs.pingcap.com/tidbcloudlake/object-pick.md): Creates a new JSON object containing only the specified keys from the input JSON object. If a specified key doesn't exist in the input object, it is omitted from the result. - Map Functions - [Map Functions](https://docs.pingcap.com/tidbcloudlake/map-functions.md): This section provides reference information for the map functions in TiDB Cloud Lake. Map functions allow you to create, manipulate, and extract information from map data structures (key-value pairs). - [MAP_CAT](https://docs.pingcap.com/tidbcloudlake/map-cat.md): Returns the concatenatation of two MAPs. - [MAP_CONTAINS_KEY](https://docs.pingcap.com/tidbcloudlake/map-contains-key.md): Determines whether the specified MAP contains the specified key. - [MAP_DELETE](https://docs.pingcap.com/tidbcloudlake/map-delete.md): Returns an existing MAP with one or more keys removed. - [MAP_FILTER](https://docs.pingcap.com/tidbcloudlake/map-filter.md): Filters key-value pairs in a JSON object based on a specified condition, defined using a lambda expression. - [MAP_INSERT](https://docs.pingcap.com/tidbcloudlake/map-insert.md): Returns a new MAP consisting of the input MAP with a new key-value pair inserted (an existing key updated with a new value). - [MAP_KEYS](https://docs.pingcap.com/tidbcloudlake/map-keys.md): Returns the keys in a map. - [MAP_PICK](https://docs.pingcap.com/tidbcloudlake/map-pick.md): Returns a new MAP containing the specified key-value pairs from an existing MAP. - [MAP_SIZE](https://docs.pingcap.com/tidbcloudlake/map-size.md): Returns the size of a MAP. - [MAP_TRANSFORM_KEYS](https://docs.pingcap.com/tidbcloudlake/map-transform-keys.md): Applies a transformation to each key in a JSON object using a lambda expression. - [MAP_TRANSFORM_VALUES](https://docs.pingcap.com/tidbcloudlake/map-transform-values.md): Applies a transformation to each value in a JSON object using a lambda expression. - [MAP_VALUES](https://docs.pingcap.com/tidbcloudlake/map-values.md): Returns the values in a map. - Type Conversion - [AS_ARRAY](https://docs.pingcap.com/tidbcloudlake/as-array.md): Strict casting VARIANT values to ARRAY data type. If the input data type is not VARIANT, the output is NULL. If the type of value in the VARIANT does not match the output value, the output is NULL. - [AS_BINARY](https://docs.pingcap.com/tidbcloudlake/as-binary.md): Strict casting VARIANT values to BINARY data type. If the input data type is not VARIANT, the output is NULL. If the type of value in the VARIANT does not match the output value, the output is NULL. - [AS_BOOLEAN](https://docs.pingcap.com/tidbcloudlake/as-boolean.md): Strict casting VARIANT values to BOOLEAN data type. If the input data type is not VARIANT, the output is NULL. If the type of value in the VARIANT does not match the output value, the output is NULL. - [AS_DATE](https://docs.pingcap.com/tidbcloudlake/as-date.md): Strict casting VARIANT values to DATE data type. If the input data type is not VARIANT, the output is NULL. If the type of value in the VARIANT does not match the output value, the output is NULL. - [AS_DECIMAL](https://docs.pingcap.com/tidbcloudlake/as-decimal.md): Strict casting VARIANT values to DECIMAL data type. If the input data type is not VARIANT, the output is NULL. If the type of value in the VARIANT does not match the output value, the output is NULL. - [AS_FLOAT](https://docs.pingcap.com/tidbcloudlake/as-float.md): Strict casting VARIANT values to DOUBLE data type. If the input data type is not VARIANT, the output is NULL. If the type of value in the VARIANT does not match the output value, the output is NULL. - [AS_INTEGER](https://docs.pingcap.com/tidbcloudlake/as-integer.md): Strict casting VARIANT values to BIGINT data type. If the input data type is not VARIANT, the output is NULL. If the type of value in the VARIANT does not match the output value, the output is NULL. - [AS_OBJECT](https://docs.pingcap.com/tidbcloudlake/as-object.md): Strict casting VARIANT values to OBJECT data type. If the input data type is not VARIANT, the output is NULL. If the type of value in the VARIANT does not match the output value, the output is NULL. - [AS_STRING](https://docs.pingcap.com/tidbcloudlake/as-string.md): Strict casting VARIANT values to VARCHAR data type. If the input data type is not VARIANT, the output is NULL. If the type of value in the VARIANT does not match the output value, the output is NULL. - [Type Conversion Functions](https://docs.pingcap.com/tidbcloudlake/type-conversion-functions.md): This section provides reference information for type conversion functions in TiDB Cloud Lake. These functions enable strict casting of VARIANT values to other SQL data types. - Type Predicate - [Type Predicate Functions](https://docs.pingcap.com/tidbcloudlake/type-predicate-functions.md): This section provides reference information for type predicate functions in TiDB Cloud Lake. These functions enable type checking, validation, and conversion of JSON values. - [IS_ARRAY](https://docs.pingcap.com/tidbcloudlake/is-array.md): Checks if the input value is a JSON array. Please note that a JSON array is not the same as the ARRAY data type. A JSON array is a data structure commonly used in JSON, representing an ordered collection of values enclosed within square brackets []. It is a flexible format for organizing and exchanging various data types, including strings, numbers, booleans, objects, and nulls. - [IS_BOOLEAN](https://docs.pingcap.com/tidbcloudlake/is-boolean.md): Checks if the input JSON value is a boolean. - [IS_FLOAT](https://docs.pingcap.com/tidbcloudlake/is-float.md): Checks if the input JSON value is a float. - [IS_INTEGER](https://docs.pingcap.com/tidbcloudlake/is-integer.md): Checks if the input JSON value is an integer. - [IS_NULL_VALUE](https://docs.pingcap.com/tidbcloudlake/is-null-value.md): Checks whether the input value is a JSON null. Please note that this function examines JSON null, not SQL NULL. To check if a value is SQL NULL, use IS_NULL. - [IS_OBJECT](https://docs.pingcap.com/tidbcloudlake/is-object.md): Checks if the input value is a JSON object. - [IS_STRING](https://docs.pingcap.com/tidbcloudlake/is-string.md): Checks if the input JSON value is a string. - Vector Functions - [COSINE_DISTANCE](https://docs.pingcap.com/tidbcloudlake/cosine-distance.md): Measuring similarity using the cosine_distance function in TiDB Cloud Lake. - [L2_DISTANCE](https://docs.pingcap.com/tidbcloudlake/l2-distance.md): Calculates the Euclidean (L2) distance between two vectors, measuring the straight-line distance between them in vector space. - [L1_DISTANCE](https://docs.pingcap.com/tidbcloudlake/l1-distance.md): Calculates the Manhattan (L1) distance between two vectors, measuring the sum of absolute differences between corresponding elements. - [INNER_PRODUCT](https://docs.pingcap.com/tidbcloudlake/inner-product.md): Calculates the inner product (dot product) of two vectors, which measures the similarity and projection between vectors. - [VECTOR_DIMS](https://docs.pingcap.com/tidbcloudlake/vector-dims.md): Returns the dimensionality (number of elements) of a vector. - [VECTOR_NORM](https://docs.pingcap.com/tidbcloudlake/vector-norm.md): Calculates the L2 norm (Euclidean norm) of a vector, which represents the length or magnitude of the vector. - [Vector Functions](https://docs.pingcap.com/tidbcloudlake/vector-functions.md): Vector functions in TiDB Cloud Lake for vector operations and analysis. - Hash Functions - [BLAKE3](https://docs.pingcap.com/tidbcloudlake/blake.md): Calculates a BLAKE3 256-bit checksum for a string. The value is returned as a string of 64 hexadecimal digits or NULL if the argument was NULL. - [CITY64WITHSEED](https://docs.pingcap.com/tidbcloudlake/city-withseed.md): Calculates a City64WithSeed 64-bit hash for a string. - [Hash Functions](https://docs.pingcap.com/tidbcloudlake/hash-functions.md): This page provides a comprehensive overview of Hash functions in TiDB Cloud Lake, organized by functionality for easy reference. - [MD5](https://docs.pingcap.com/tidbcloudlake/md.md): Calculates an MD5 128-bit checksum for a string. The value is returned as a string of 32 hexadecimal digits or NULL if the argument was NULL. - [SHA](https://docs.pingcap.com/tidbcloudlake/sha.md): Calculates an SHA-1 160-bit checksum for the string, as described in RFC 3174 (Secure Hash Algorithm). The value is returned as a string of 40 hexadecimal digits or NULL if the argument was NULL. - [SHA1](https://docs.pingcap.com/tidbcloudlake/sha-sql.md): Alias for SHA. - [SHA2](https://docs.pingcap.com/tidbcloudlake/sha-functions.md): Calculates the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512). If the hash length is not one of the permitted values, the return value is NULL. Otherwise, the function result is a hash value containing the desired number of bits as a string of hexadecimal digits. - [SIPHASH](https://docs.pingcap.com/tidbcloudlake/siphash.md): Alias for SIPHASH64. - [SIPHASH64](https://docs.pingcap.com/tidbcloudlake/siphash-sql.md): Produces a 64-bit SipHash hash value. - [XXHASH32](https://docs.pingcap.com/tidbcloudlake/xxhash.md): Calculates an xxHash32 32-bit hash value for a string. The value is returned as a UInt32 or NULL if the argument was NULL. - [XXHASH64](https://docs.pingcap.com/tidbcloudlake/xxhash-sql.md): Calculates an xxHash64 64-bit hash value for a string. The value is returned as a UInt64 or NULL if the argument was NULL. - UUID Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/uuid-functions.md): This page provides reference information for the UUID-related functions in TiDB Cloud Lake. These functions generate and work with Universally Unique Identifiers (UUIDs). - [GEN_RANDOM_UUID](https://docs.pingcap.com/tidbcloudlake/gen-random-uuid.md): Generates a random UUID based on version 7, starting from version 1.2.658. Previously, this function generated UUIDs based on version 4. - [UUID](https://docs.pingcap.com/tidbcloudlake/uuid-sql.md): Alias for GEN_RANDOM_UUID. - IP Address Functions - [IP Address Functions](https://docs.pingcap.com/tidbcloudlake/ip-address-functions.md): This page provides reference information for the IP address-related functions in TiDB Cloud Lake. These functions help convert between string and numeric representations of IP addresses. - [INET_ATON](https://docs.pingcap.com/tidbcloudlake/inet-aton.md): Converts an IPv4 address to a 32-bit integer. - [INET_NTOA](https://docs.pingcap.com/tidbcloudlake/inet-ntoa.md): Converts a 32-bit integer to an IPv4 address. - [IPV4_NUM_TO_STRING](https://docs.pingcap.com/tidbcloudlake/ipv4-num-to-string.md): Alias for INET_NTOA. - [IPV4_STRING_TO_NUM](https://docs.pingcap.com/tidbcloudlake/ipv4-string-to-num.md): Alias for INET_ATON. - [TRY_INET_ATON](https://docs.pingcap.com/tidbcloudlake/try-inet-aton.md): try_inet_aton function is used to take the dotted-quad representation of an IPv4 address as a string and returns the numeric value of the given IP address in form of an integer. - [TRY_INET_NTOA](https://docs.pingcap.com/tidbcloudlake/try-inet-ntoa.md): Takes an IPv4 address in network byte order and then returns the address as a dotted-quad string representation. - [TRY_IPV4_NUM_TO_STRING](https://docs.pingcap.com/tidbcloudlake/try-ipv4-num-to-string.md): Alias for TRY_INET_NTOA. - [TRY_IPV4_STRING_TO_NUM](https://docs.pingcap.com/tidbcloudlake/try-ipv4-string-to-num.md): Alias for TRY_INET_ATON. - Context Functions - [CONNECTION_ID](https://docs.pingcap.com/tidbcloudlake/connection-id.md): Returns the connection ID for the current connection. - [CURRENT_CATALOG](https://docs.pingcap.com/tidbcloudlake/current-catalog.md): Returns the name of the catalog currently in use for the session. - [CURRENT_USER](https://docs.pingcap.com/tidbcloudlake/current-user.md): Returns the user name and host name combination for the account that the server used to authenticate the current client. This account determines your access privileges. The return value is a string in the utf8 character set. - [DATABASE](https://docs.pingcap.com/tidbcloudlake/database-function.md): Returns the name of the currently selected database. If no database is selected, then this function returns default. - [Context Functions](https://docs.pingcap.com/tidbcloudlake/context-functions.md): This page provides reference information for the context-related functions in TiDB Cloud Lake. These functions return information about the current session, database, or system context. - [LAST_QUERY_ID](https://docs.pingcap.com/tidbcloudlake/last-query-id.md): Returns the ID of a query in the current session based on its order. - [VERSION](https://docs.pingcap.com/tidbcloudlake/version.md): Returns the current version of LakeQuery. - System Functions - [CLUSTERING_INFORMATION](https://docs.pingcap.com/tidbcloudlake/clustering-information.md): Returns clustering information of a table. - [FUSE_BLOCK](https://docs.pingcap.com/tidbcloudlake/fuse-block.md): Returns the block information of the latest or specified snapshot of a table. For more information about what is block in TiDB Cloud Lake, see What are Snapshot, Segment, and Block?. - [FUSE_COLUMN](https://docs.pingcap.com/tidbcloudlake/fuse-column.md): Returns the column information of the latest or specified snapshot of a table. For more information about what is block in TiDB Cloud Lake, see What are Snapshot, Segment, and Block?. - [FUSE_ENCODING](https://docs.pingcap.com/tidbcloudlake/fuse-encoding.md): Returns the encoding types applied to a specific column within a table. It helps you understand how data is compressed and stored in a native format within the table. - [FUSE_SEGMENT](https://docs.pingcap.com/tidbcloudlake/fuse-segment.md): Returns the segment information of a specified table snapshot. For more information about what is segment in TiDB Cloud Lake, see What are Snapshot, Segment, and Block?. - [FUSE_SNAPSHOT](https://docs.pingcap.com/tidbcloudlake/fuse-snapshot.md): Returns the snapshot information of a table. For more information about what is snapshot in TiDB Cloud Lake, see What are Snapshot, Segment, and Block?. - [FUSE_STATISTIC](https://docs.pingcap.com/tidbcloudlake/fuse-statistic.md): note This function is deprecated. Use SHOW STATISTICS instead to view table statistics.. - [FUSE_TAG](https://docs.pingcap.com/tidbcloudlake/fuse-tag.md): Returns the snapshot tags of a table. For more information about snapshot tags, see Snapshot Tags. - [FUSE_TIME_TRAVEL_SIZE](https://docs.pingcap.com/tidbcloudlake/fuse-time-travel-size.md): Calculates the storage size of historical data (for Time Travel) for tables. - [FUSE_VIRTUAL_COLUMN](https://docs.pingcap.com/tidbcloudlake/fuse-virtual-column.md): Returns the virtual column information of the latest or specified snapshot of a table. For details, see Virtual Column. - [System Functions](https://docs.pingcap.com/tidbcloudlake/system-functions-sql.md): This page provides reference information for the system-related functions in TiDB Cloud Lake. These functions help you analyze and monitor the internal storage and performance aspects of your TiDB Cloud Lake deployment. - Table Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/table-functions.md): This page provides reference information for the table functions in TiDB Cloud Lake. Table functions return a set of rows (similar to a table) and can be used in the FROM clause of a query. - [INFER_SCHEMA](https://docs.pingcap.com/tidbcloudlake/infer-schema.md): Automatically detects the file metadata schema and retrieves the column definitions. - [INSPECT_PARQUET](https://docs.pingcap.com/tidbcloudlake/inspect-parquet.md): Retrieves a table of comprehensive metadata from a staged Parquet file, including the following columns. - [LIST_STAGE](https://docs.pingcap.com/tidbcloudlake/list-stage.md): Lists files in a stage. This allows you to filter files in a stage based on their extensions and obtain comprehensive details about each file. The function is similar to the DDL command LIST STAGE FILES, but provides you the flexibility to retrieve specific file information with the SELECT statement, such as file name, size, MD5 hash, last modified timestamp, and creator, rather than all file information. - [GENERATE_SERIES](https://docs.pingcap.com/tidbcloudlake/generate-series.md): Generates a dataset starting from a specified point, ending at another specified point, and optionally with an incrementing value. The GENERATE_SERIES function works with the following data types. - [FLATTEN](https://docs.pingcap.com/tidbcloudlake/flatten.md): Transforms nested JSON or array data into a tabular format, where each element or field is represented as a separate row. - [SYSTEM$FUSE_AMEND](https://docs.pingcap.com/tidbcloudlake/system-fuse-amend.md): Recovers table data from S3-compatible object storage. - [FUSE_VACUUM_TEMPORARY_TABLE](https://docs.pingcap.com/tidbcloudlake/fuse-vacuum-temporary-table.md): Temporary tables are typically cleaned up automatically at session end (details in CREATE TEMP TABLE). However, this process can fail due to events like query node crashes or abnormal session terminations, leaving orphaned temporary files. - [GET_LINEAGE](https://docs.pingcap.com/tidbcloudlake/get-lineage.md): Returns upstream or downstream lineage for a table, view, stage, or column. Each returned row represents one source-to-target relationship in the lineage path. - [ICEBERG_MANIFEST](https://docs.pingcap.com/tidbcloudlake/iceberg-manifest.md): Returns metadata about manifest files of an Iceberg table, including file paths, partitioning details, and snapshot associations. - [ICEBERG_SNAPSHOT](https://docs.pingcap.com/tidbcloudlake/iceberg-snapshot.md): Returns metadata about snapshots of an Iceberg table, including information about data changes, operations, and summary statistics. - [POLICY_REFERENCES](https://docs.pingcap.com/tidbcloudlake/policy-references.md): Returns the associations between security policies (Masking Policy or Row Access Policy) and tables/views. You can query by policy name to find all tables using it, or by table name to find all policies applied to it. - [RESULT_SCAN](https://docs.pingcap.com/tidbcloudlake/result-scan.md): Retrieves the cached result of a previous query by its query ID. - [SYSTEM$SET_CACHE_CAPACITY](https://docs.pingcap.com/tidbcloudlake/set-cache-capacity.md): Adjusts the capacity of a named cache at runtime. - [SHOW_GRANTS](https://docs.pingcap.com/tidbcloudlake/show-grants-sql.md): Lists privileges granted to roles, role assignments for users, or privileges on a specific object. - [SHOW_VARIABLES](https://docs.pingcap.com/tidbcloudlake/show-variables-sql.md): Displays all session variables and their details, such as names, values, and types. - [STREAM_STATUS](https://docs.pingcap.com/tidbcloudlake/stream-status.md): Provides information about the status of a specified stream, yielding a single-column result (has_data) that can take on values of true or false. - [TAG_REFERENCES](https://docs.pingcap.com/tidbcloudlake/tag-references.md): Returns all tags assigned to a specified database object. - [TASK_HISTORY](https://docs.pingcap.com/tidbcloudlake/task-history.md): Displays task running history given variables. - Sequence Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/sequence-functions-overview.md): This section provides reference information for the sequence functions in TiDB Cloud Lake. Sequence functions allow you to work with sequence objects, which generate unique, auto-incrementing numeric values. - [NEXTVAL](https://docs.pingcap.com/tidbcloudlake/nextval.md): Retrieves the next value from a sequence. - Data Anonymization Functions - [FEISTEL_OBFUSCATE](https://docs.pingcap.com/tidbcloudlake/feistel-obfuscate.md): Deterministically obfuscate integers (e.g. IDs or phone numbers) while preserving bit length and value cardinality so joins still work. - [Data Anonymization Functions](https://docs.pingcap.com/tidbcloudlake/data-anonymization-functions.md): Data anonymization is the process of altering or removing personally identifiable information (PII) from data sets to protect individual privacy. Its goal is to transform data so it cannot be linked back to specific individuals, while preserving the data's utility for analysis, research, and testing. - [MARKOV_GENERATE](https://docs.pingcap.com/tidbcloudlake/markov-generate.md): Using the model trained by MARKOV_TRAIN to anonymize the dataset. - [OBFUSCATE](https://docs.pingcap.com/tidbcloudlake/obfuscate.md): Dataset anonymization. This is a quick tool, and for more complex scenarios, it is recommended to directly use the underlying function MARKOV_TRAIN, MARKOV_GENERATE, FEISTEL_OBFUSCATE. - Test Functions - [Test Functions](https://docs.pingcap.com/tidbcloudlake/test-functions.md): This page provides reference information for the test functions in TiDB Cloud Lake. These functions are primarily used for testing and debugging purposes. - [SLEEP](https://docs.pingcap.com/tidbcloudlake/sleep.md): Sleeps seconds seconds on each data block. - Other Functions - [Overview](https://docs.pingcap.com/tidbcloudlake/other-functions.md): This section collects assorted utilities that do not fit into the major functional groups. - [ASSUME_NOT_NULL](https://docs.pingcap.com/tidbcloudlake/assume-not-null.md): Results in an equivalent non-Nullable value for a Nullable type. In case the original value is NULL the result is undetermined. - [EXISTS](https://docs.pingcap.com/tidbcloudlake/exists.md): The exists condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. - [GROUPING](https://docs.pingcap.com/tidbcloudlake/grouping.md): Returns a bit mask indicating which GROUP BY expressions are not included in the current grouping set. Bits are assigned with the rightmost argument corresponding to the least-significant bit; each bit is 0 if the corresponding expression is included in the grouping criteria of the grouping set generating the current result row, and 1 if it is not included. - [HUMANIZE_NUMBER](https://docs.pingcap.com/tidbcloudlake/humanize-number.md): Returns a readable number. - [HUMANIZE_SIZE](https://docs.pingcap.com/tidbcloudlake/humanize-size.md): Returns the readable size with a suffix(KiB, MiB, etc). - [READ_FILE](https://docs.pingcap.com/tidbcloudlake/read-file.md): Reads a file from a stage and returns its raw bytes. - [REMOVE_NULLABLE](https://docs.pingcap.com/tidbcloudlake/remove-nullable.md): Alias for ASSUME_NOT_NULL. - [TO_NULLABLE](https://docs.pingcap.com/tidbcloudlake/nullable.md): Converts a value to its nullable equivalent. - [TYPEOF](https://docs.pingcap.com/tidbcloudlake/typeof.md): TYPEOF function is used to return the name of a data type. - [Stored Procedure & Scripting](https://docs.pingcap.com/tidbcloudlake/stored-procedure-scripting.md): Stored procedures in TiDB Cloud Lake let you package SQL logic that runs on the server with access to control flow, variables, cursors, and dynamic statements. This page explains how to create procedures and write the inline scripting that powers them. - General Reference - [Architecture](https://docs.pingcap.com/tidbcloudlake/tidb-cloud-lake-architecture.md): TiDB Cloud Lake Architecture. - Implementation Deep Dives - [Fuse Engine](https://docs.pingcap.com/tidbcloudlake/how-fuse-engine-works.md): Fuse Engine is TiDB Cloud Lake's core storage engine, optimized for managing petabyte-scale data efficiently on cloud object storage. By default, tables created in TiDB Cloud Lake automatically use this engine (ENGINE=FUSE). Inspired by Git, its snapshot-based design enables powerful data versioning (like Time Travel) and provides high query performance through advanced pruning and indexing. - [Optimizer](https://docs.pingcap.com/tidbcloudlake/how-optimizer-works.md): TiDB Cloud Lake's query optimizer orchestrates a series of transformations that turn SQL text into an executable plan. The optimizer builds an abstract representation of the query, enriches it with real-time statistics, applies rule-based rewrites, explores join alternatives, and finally picks the cheapest physical operators. - [JSON](https://docs.pingcap.com/tidbcloudlake/how-json-variant-works.md): TiDB Cloud Lake reimagines JSON analytics by pairing a native binary layout with automatic JSON indexing so semi-structured data behaves like first-class columns. - [Data Sharing](https://docs.pingcap.com/tidbcloudlake/how-data-sharing-works.md): Different teams need different parts of the same data. Traditional solutions copy data multiple times - expensive and hard to maintain. - [Editions](https://docs.pingcap.com/tidbcloudlake/editions.md): TiDB Cloud Lake comes in three editions Personal, Business, and Dedicated, that you can choose from to serve a wide range of needs and ensure optimal performance for different use cases. - [Platforms & Regions](https://docs.pingcap.com/tidbcloudlake/platforms-regions.md): Learn about the supported cloud platforms and regions for TiDB Cloud Lake. - Benchmarks - [Data Ingestion Benchmark](https://docs.pingcap.com/tidbcloudlake/benchmark-data-ingestion.md): This page presents a benchmark comparison of data ingestion performance and cost between TiDB Cloud Lake and Snowflake, focusing on TPC-H SF100 dataset loading, ClickBench Hits dataset loading, and freshness benchmarks. - [TPC-H SF100 Benchmark](https://docs.pingcap.com/tidbcloudlake/benchmark-tpch-sf100.md): This guide presents a performance and cost comparison between TiDB Cloud Lake and Snowflake using the TPC-H SF100 dataset. It includes data loading and query execution benchmarks, along with instructions to reproduce the results. - [TPC-H SF1000 Benchmark](https://docs.pingcap.com/tidbcloudlake/benchmark-tpch-sf1000.md): This guide presents a performance comparison of Small, Medium, and Large TiDB Cloud Lake Warehouses using the TPC-H SF1000 dataset. It includes query execution times for each warehouse size and instructions to reproduce the benchmark results. - [Support Services](https://docs.pingcap.com/tidbcloudlake/support-services.md): TiDB Cloud Lake provides comprehensive Support Services for our TiDB Cloud Lake users and customers. Our objective is to deliver exceptional support that reflects the TiDB Cloud Lake product's core values – high performance, ease of use, and fast, high-quality results.