LogoLogo
latest
latest
  • Introduction
  • Basics
    • Concepts
      • Pinot storage model
      • Architecture
      • Components
        • Cluster
          • Tenant
          • Server
          • Controller
          • Broker
          • Minion
        • Table
          • Segment
            • Deep Store
            • Segment threshold
            • Segment retention
          • Schema
          • Time boundary
        • Pinot Data Explorer
    • Getting Started
      • Running Pinot locally
      • Running Pinot in Docker
      • Quick Start Examples
      • Running in Kubernetes
      • Running on public clouds
        • Running on Azure
        • Running on GCP
        • Running on AWS
      • Create and update a table configuration
      • Batch import example
      • Stream ingestion example
      • HDFS as Deep Storage
      • Troubleshooting Pinot
      • Frequently Asked Questions (FAQs)
        • General
        • Pinot On Kubernetes FAQ
        • Ingestion FAQ
        • Query FAQ
        • Operations FAQ
    • Indexing
      • Bloom filter
      • Dictionary index
      • Forward index
      • FST index
      • Geospatial
      • Inverted index
      • JSON index
      • Native text index
      • Range index
      • Star-tree index
      • Text search support
      • Timestamp index
      • Vector index
    • Release notes
      • 1.3.0
      • 1.2.0
      • 1.1.0
      • 1.0.0
      • 0.12.1
      • 0.12.0
      • 0.11.0
      • 0.10.0
      • 0.9.3
      • 0.9.2
      • 0.9.1
      • 0.9.0
      • 0.8.0
      • 0.7.1
      • 0.6.0
      • 0.5.0
      • 0.4.0
      • 0.3.0
      • 0.2.0
      • 0.1.0
    • Recipes
      • Connect to Streamlit
      • Connect to Dash
      • Visualize data with Redash
      • GitHub Events Stream
  • For Users
    • Query
      • Querying Pinot
      • Query Syntax
        • Explain Plan (Single-Stage)
        • Filtering with IdSet
        • GapFill Function For Time-Series Dataset
        • Grouping Algorithm
        • JOINs
        • Lookup UDF Join
      • Query Options
      • Query Quotas
      • Query using Cursors
      • Multi-stage query
        • Understanding Stages
        • Stats
        • Optimizing joins
        • Join strategies
          • Random + broadcast join strategy
          • Query time partition join strategy
          • Colocated join strategy
          • Lookup join strategy
        • Hints
        • Operator Types
          • Aggregate
          • Filter
          • Join
          • Intersect
          • Leaf
          • Literal
          • Mailbox receive
          • Mailbox send
          • Minus
          • Sort or limit
          • Transform
          • Union
          • Window
        • Stage-Level Spooling
      • Explain plan
    • APIs
      • Broker Query API
        • Query Response Format
      • Broker GRPC API
      • Controller Admin API
      • Controller API Reference
    • External Clients
      • JDBC
      • Java
      • Python
      • Golang
    • Tutorials
      • Use OSS as Deep Storage for Pinot
      • Ingest Parquet Files from S3 Using Spark
      • Creating Pinot Segments
      • Use S3 as Deep Storage for Pinot
      • Use S3 and Pinot in Docker
      • Batch Data Ingestion In Practice
      • Schema Evolution
  • For Developers
    • Basics
      • Extending Pinot
        • Writing Custom Aggregation Function
        • Segment Fetchers
      • Contribution Guidelines
      • Code Setup
      • Code Modules and Organization
      • Dependency Management
      • Update documentation
    • Advanced
      • Data Ingestion Overview
      • Ingestion Aggregations
      • Ingestion Transformations
      • Null value support
      • Use the multi-stage query engine (v2)
      • Advanced Pinot Setup
    • Plugins
      • Write Custom Plugins
        • Input Format Plugin
        • Filesystem Plugin
        • Batch Segment Fetcher Plugin
        • Stream Ingestion Plugin
    • Design Documents
      • Segment Writer API
  • For Operators
    • Deployment and Monitoring
      • Set up cluster
      • Server Startup Status Checkers
      • Set up table
      • Set up ingestion
      • Decoupling Controller from the Data Path
      • Segment Assignment
      • Instance Assignment
      • Rebalance
        • Rebalance Servers
          • Examples and Scenarios
        • Rebalance Brokers
        • Rebalance Tenant
      • Separating data storage by age
        • Using multiple tenants
        • Using multiple directories
      • Pinot managed Offline flows
      • Minion merge rollup task
      • Consistent Push and Rollback
      • Access Control
      • Monitoring
      • Tuning
        • Tuning Default MMAP Advice
        • Real-time
        • Routing
        • Query Routing using Adaptive Server Selection
        • Query Scheduling
      • Upgrading Pinot with confidence
      • Managing Logs
      • OOM Protection Using Automatic Query Killing
      • Pause ingestion based on resource utilization
    • Command-Line Interface (CLI)
    • Configuration Recommendation Engine
    • Tutorials
      • Authentication
        • Basic auth access control
        • ZkBasicAuthAccessControl
      • Configuring TLS/SSL
      • Build Docker Images
      • Running Pinot in Production
      • Kubernetes Deployment
      • Amazon EKS (Kafka)
      • Amazon MSK (Kafka)
      • Monitor Pinot using Prometheus and Grafana
      • Performance Optimization Configurations
      • Segment Operations Throttling
      • Reload a table segment
  • Configuration Reference
    • Cluster
    • Controller
    • Broker
    • Server
    • Table
    • Ingestion
    • Schema
    • Database
    • Ingestion Job Spec
    • Monitoring Metrics
    • Plugin Reference
      • Stream Ingestion Connectors
      • VAR_POP
      • VAR_SAMP
      • STDDEV_POP
      • STDDEV_SAMP
    • Dynamic Environment
  • Manage Data
    • Import Data
      • SQL Insert Into From Files
      • Upload Pinot segment Using CommandLine
      • Batch Ingestion
        • Spark
        • Flink
        • Hadoop
        • Backfill Data
        • Dimension table
      • Stream Ingestion
        • Ingest streaming data from Apache Kafka
        • Ingest streaming data from Amazon Kinesis
        • Ingest streaming data from Apache Pulsar
        • Configure indexes
        • Stream ingestion with CLP
      • Upsert and Dedup
        • Stream ingestion with Upsert
        • Segment compaction on upserts
        • Stream ingestion with Dedup
      • Supported Data Formats
      • File Systems
        • Amazon S3
        • Azure Data Lake Storage
        • HDFS
        • Google Cloud Storage
      • Complex Type (Array, Map) Handling
        • Complex Type Examples (Unnest)
      • Ingest records with dynamic schemas
  • Functions
    • Aggregation Functions
    • Transformation Functions
    • Array Functions
    • Funnel Analysis Functions
    • Hash Functions
    • JSON Functions
    • User-Defined Functions (UDFs)
    • URL Functions
    • Unique Count and cardinality Estimation Functions
  • Window Functions
  • (Deprecating) Function List
    • ABS
    • ADD
    • ago
    • EXPR_MIN / EXPR_MAX
    • ARRAY_AGG
    • arrayConcatDouble
    • arrayConcatFloat
    • arrayConcatInt
    • arrayConcatLong
    • arrayConcatString
    • arrayContainsInt
    • arrayContainsString
    • arrayDistinctInt
    • arrayDistinctString
    • arrayIndexOfInt
    • arrayIndexOfString
    • ARRAYLENGTH
    • arrayRemoveInt
    • arrayRemoveString
    • arrayReverseInt
    • arrayReverseString
    • arraySliceInt
    • arraySliceString
    • arraySortInt
    • arraySortString
    • arrayUnionInt
    • arrayUnionString
    • AVGMV
    • Base64
    • caseWhen
    • ceil
    • CHR
    • codepoint
    • concat
    • count
    • COUNTMV
    • COVAR_POP
    • COVAR_SAMP
    • day
    • dayOfWeek
    • dayOfYear
    • DISTINCT
    • DISTINCTCOUNT
    • DISTINCTCOUNTMV
    • DISTINCT_COUNT_OFF_HEAP
    • SEGMENTPARTITIONEDDISTINCTCOUNT
    • DISTINCTCOUNTBITMAP
    • DISTINCTCOUNTBITMAPMV
    • DISTINCTCOUNTHLL
    • DISTINCTCOUNTHLLMV
    • DISTINCTCOUNTRAWHLL
    • DISTINCTCOUNTRAWHLLMV
    • DISTINCTCOUNTSMARTHLL
    • DISTINCTCOUNTHLLPLUS
    • DISTINCTCOUNTULL
    • DISTINCTCOUNTTHETASKETCH
    • DISTINCTCOUNTRAWTHETASKETCH
    • DISTINCTSUM
    • DISTINCTSUMMV
    • DISTINCTAVG
    • DISTINCTAVGMV
    • DIV
    • DATETIMECONVERT
    • DATETRUNC
    • exp
    • FIRSTWITHTIME
    • FLOOR
    • FrequentLongsSketch
    • FrequentStringsSketch
    • FromDateTime
    • FromEpoch
    • FromEpochBucket
    • FUNNELCOUNT
    • FunnelCompleteCount
    • FunnelMaxStep
    • FunnelMatchStep
    • GridDistance
    • Histogram
    • hour
    • isSubnetOf
    • JSONFORMAT
    • JSONPATH
    • JSONPATHARRAY
    • JSONPATHARRAYDEFAULTEMPTY
    • JSONPATHDOUBLE
    • JSONPATHLONG
    • JSONPATHSTRING
    • jsonextractkey
    • jsonextractscalar
    • LAG
    • LASTWITHTIME
    • LEAD
    • length
    • ln
    • lower
    • lpad
    • ltrim
    • max
    • MAXMV
    • MD5
    • millisecond
    • min
    • minmaxrange
    • MINMAXRANGEMV
    • MINMV
    • minute
    • MOD
    • mode
    • month
    • mult
    • now
    • percentile
    • percentileest
    • percentileestmv
    • percentilemv
    • percentiletdigest
    • percentiletdigestmv
    • percentilekll
    • percentilerawkll
    • percentilekllmv
    • percentilerawkllmv
    • quarter
    • regexpExtract
    • regexpReplace
    • remove
    • replace
    • reverse
    • round
    • roundDecimal
    • ROW_NUMBER
    • rpad
    • rtrim
    • second
    • sha
    • sha256
    • sha512
    • sqrt
    • startswith
    • ST_AsBinary
    • ST_AsText
    • ST_Contains
    • ST_Distance
    • ST_GeogFromText
    • ST_GeogFromWKB
    • ST_GeometryType
    • ST_GeomFromText
    • ST_GeomFromWKB
    • STPOINT
    • ST_Polygon
    • strpos
    • ST_Union
    • SUB
    • substr
    • sum
    • summv
    • TIMECONVERT
    • timezoneHour
    • timezoneMinute
    • ToDateTime
    • ToEpoch
    • ToEpochBucket
    • ToEpochRounded
    • TOJSONMAPSTR
    • toGeometry
    • toSphericalGeography
    • trim
    • upper
    • Url
    • UTF8
    • VALUEIN
    • week
    • year
    • Extract
    • yearOfWeek
    • FIRST_VALUE
    • LAST_VALUE
    • ST_GeomFromGeoJSON
    • ST_GeogFromGeoJSON
    • ST_AsGeoJSON
  • Reference
    • Single-stage query engine (v1)
    • Multi-stage query engine (v2)
    • Troubleshooting
      • Troubleshoot issues with the multi-stage query engine (v2)
      • Troubleshoot issues with ZooKeeper znodes
      • Realtime Ingestion Stopped
  • RESOURCES
    • Community
    • Team
    • Blogs
    • Presentations
    • Videos
  • Integrations
    • Tableau
    • Trino
    • ThirdEye
    • Superset
    • Presto
    • Spark-Pinot Connector
  • Contributing
    • Contribute Pinot documentation
    • Style guide
Powered by GitBook
On this page
  • urlProtocol
  • urlDomain
  • urlDomainWithoutWWW
  • urlTopLevelDomain
  • urlFirstSignificantSubdomain
  • cutToFirstSignificantSubdomain
  • cutToFirstSignificantSubdomainWithWWW
  • urlPort
  • urlPath
  • urlPathWithQuery
  • urlQueryString
  • urlFragment
  • urlQueryStringAndFragment
  • extractURLParameter
  • extractURLParameters
  • extractURLParameterNames
  • urlHierarchy
  • urlPathHierarchy
  • urlEncode
  • urlDecode
  • urlEncodeFormComponent
  • urlDecodeFormComponent
  • urlNetloc
  • cutWWW
  • cutQueryString
  • cutFragment
  • cutQueryStringAndFragment
  • cutURLParameter
  • cutURLParameters

Was this helpful?

Edit on GitHub
Export as PDF
  1. Functions

URL Functions

This document describes the URL utility functions available as scalar functions. These functions allow you to extract, transform, and manipulate various components of a URL such as the protocol, domain, port, path, query parameters, and more.

urlProtocol

Description: Extracts the protocol (scheme) from the URL.

Syntax:

urlProtocol(url: String) → String

Parameters:

• url: URL string.

Returns:

The protocol (e.g., "http", "https") or null if the URL is invalid.

Example:

SELECT urlProtocol('https://example.com/path');
// Returns: "https"

urlDomain

Description: Extracts the domain from the URL.

Syntax:

urlDomain(url: String) → String

Parameters:

• url: URL string.

Returns:

The full domain (e.g., "www.example.com") or null if the URL is invalid.

Example:

SELECT urlDomain('https://www.example.com/path');
// Returns: "www.example.com"

urlDomainWithoutWWW

Description: Extracts the domain from the URL while removing a leading "www." if present.

Syntax:

urlDomainWithoutWWW(url: String) → String

Parameters:

• url: URL string.

Returns:

The domain without the "www." prefix (e.g., "example.com") or null if invalid.

Example:

SELECT urlDomainWithoutWWW('https://www.example.com/path');
// Returns: "example.com"

urlTopLevelDomain

Description: Extracts the top-level domain (TLD) from the URL.

Syntax:

urlTopLevelDomain(url: String) → String

Parameters:

• url: URL string.

Returns:

The top-level domain (e.g., "com", "org") or null if invalid.

Example:

SELECT urlTopLevelDomain('https://example.com/path');
// Returns: "com"

urlFirstSignificantSubdomain

Description: Extracts the first significant subdomain from the URL based on standard TLD rules.

Syntax:

urlFirstSignificantSubdomain(url: String) → String

Parameters:

• url: URL string.

Returns:

The first significant subdomain (for example, for "blog.example.com", returns "example") or null if invalid.

Example:

SELECT urlFirstSignificantSubdomain('https://blog.example.com');
// Returns: "example"

Note: The logic considers common TLDs such as “com”, “net”, “org”, and “co”.

cutToFirstSignificantSubdomain

Description: Truncates the URL’s domain to include only the first significant subdomain and the top-level domain.

Syntax:

cutToFirstSignificantSubdomain(url: String) → String

Parameters:

• url: URL string.

Returns:

A truncated domain such as "example.com" (or a deeper hierarchy for non-standard TLDs) or null if invalid.

Example:

SELECT cutToFirstSignificantSubdomain('https://blog.example.com');
// Returns: "example.com"

cutToFirstSignificantSubdomainWithWWW

Description: Similar to cutToFirstSignificantSubdomain but preserves a leading "www." if it is part of the URL.

Syntax:

cutToFirstSignificantSubdomainWithWWW(url: String) → String

Parameters:

• url: URL string.

Returns:

The truncated domain with "www." preserved (if applicable) or null if invalid.

Example:

SELECT cutToFirstSignificantSubdomainWithWWW('https://www.blog.example.com');
// Returns: "www.example.com"

urlPort

Description: Extracts the port number from the URL.

Syntax:

urlPort(url: String) → int

Parameters:

• url: URL string.

Returns:

The port number, or -1 if no port is specified or if the URL is invalid.

Example:

SELECT urlPort('https://example.com:8080/path');
// Returns: 8080

urlPath

Description: Extracts the path component from the URL (excluding the query string).

Syntax:

urlPath(url: String) → String

Parameters:

• url: URL string.

Returns:

The URL path (e.g., "/path/to/resource") or null if invalid.

Example:

SELECT urlPath('https://example.com/path?query=1');
// Returns: "/path"

urlPathWithQuery

Description: Extracts the path component from the URL. (Note that this function uses the raw path as returned by the URI parser.)

Syntax:

urlPathWithQuery(url: String) → String

Parameters:

• url: URL string.

Returns:

The URL path (which may include query encoding details) or null if invalid.

Example:

SELECT urlPathWithQuery('https://example.com/path?query=1');
// Returns: "/path"

urlQueryString

Description: Extracts the query string from the URL without the leading ? and excluding the fragment.

Syntax:

urlQueryString(url: String) → String

Parameters:

• url: URL string.

Returns:

The query string (e.g., "key=value&key2=value2"), or null if absent or invalid.

Example:

SELECT urlQueryString('https://example.com/path?key=value#section');
// Returns: "key=value"

urlFragment

Description: Extracts the fragment identifier from the URL (without the # symbol).

Syntax:

urlFragment(url: String) → String

Parameters:

• url: URL string.

Returns:

The fragment (e.g., "section") or null if invalid or not present.

Example:

SELECT urlFragment('https://example.com/path#section');
// Returns: "section"

urlQueryStringAndFragment

Description: Combines the query string and fragment identifier from the URL into a single string.

Syntax:

urlQueryStringAndFragment(url: String) → String

Parameters:

• url: URL string.

Returns:

A concatenated string such as "key=value#section", or null if neither component is present.

Example:

SELECT urlQueryStringAndFragment('https://example.com/path?key=value#section');
// Returns: "key=value#section"

extractURLParameter

Description: Extracts the value of a specific query parameter from the URL. If multiple parameters with the same name exist, the first occurrence is returned.

Syntax:

extractURLParameter(url: String, name: String) → String

Parameters:

• url: URL string.

• name: Name of the parameter to extract.

Returns:

The value of the parameter, or an empty string if not found or if the URL is invalid.

Example:

SELECT extractURLParameter('https://example.com/path?page=1&lr=213', 'page');
// Returns: "1"

extractURLParameters

Description: Extracts all query parameters from the URL as an array of name=value pairs.

Syntax:

extractURLParameters(url: String) → String[]

Parameters:

• url: URL string.

Returns:

An array of query parameters (e.g., ["page=1", "lr=213"]), or an empty array if no parameters exist.

Example:

SELECT extractURLParameters('https://example.com/path?page=1&lr=213');
// Returns: ["page=1", "lr=213"]

extractURLParameterNames

Description: Extracts the names of all query parameters present in the URL.

Syntax:

extractURLParameterNames(url: String) → String[]

Parameters:

• url: URL string.

Returns:

An array of parameter names (e.g., ["page", "lr"]), or an empty array if no query parameters exist.

Example:

SELECT extractURLParameterNames('https://example.com/path?page=1&lr=213');
// Returns: ["page", "lr"]

urlHierarchy

Description: Generates a hierarchy of URLs truncated at each level of the path. The base URL (scheme and host) is always included.

Syntax:

urlHierarchy(url: String) → String[]

Parameters:

• url: URL string.

Returns:

An array of URLs representing the hierarchical levels. For example, given the URL "https://example.com/a/b/c", it returns:

[
  "https://example.com",
  "https://example.com/a",
  "https://example.com/a/b",
  "https://example.com/a/b/c"
]

Example:

SELECT urlHierarchy('https://example.com/a/b/c');
// Returns: ["https://example.com", "https://example.com/a", "https://example.com/a/b", "https://example.com/a/b/c"]

urlPathHierarchy

Description: Generates a hierarchy of the path segments from the URL. The protocol and host are excluded, and the root ("/") is not included.

Syntax:

urlPathHierarchy(url: String) → String[]

Parameters:

• url: URL string.

Returns:

An array of hierarchical path segments. For example, for the URL "https://example.com/browse/CONV-6788", it returns:

["/browse", "/browse/CONV-6788"]

Example:

SELECT urlPathHierarchy('https://example.com/browse/CONV-6788');
// Returns: ["/browse", "/browse/CONV-6788"]

urlEncode

Description: Encodes a URL string into a URL-safe format. Spaces are replaced with +.

Syntax:

urlEncode(url: String) → String

Parameters:

• url: URL string to encode.

Returns:

The URL-encoded string or null if the input is invalid.

Example:

SELECT urlEncode('https://example.com/path with space');
// Returns: "https%3A%2F%2Fexample.com%2Fpath+with+space"

urlDecode

Description: Decodes a URL-encoded string.

Syntax:

urlDecode(url: String) → String

Parameters:

• url: URL-encoded string.

Returns:

The decoded URL string or null if the input is invalid.

Example:

SELECT urlDecode('https%3A%2F%2Fexample.com%2Fpath+with+space');
// Returns: "https://example.com/path with space"

urlEncodeFormComponent

Description: Encodes a string as a URL form component following RFC-1866 standards. Spaces are encoded as +.

Syntax:

urlEncodeFormComponent(url: String) → String

Parameters:

• url: String to encode.

Returns:

The encoded string or null if the input is invalid.

Example:

SELECT urlEncodeFormComponent('Hello World!');
// Returns: "Hello+World%21"

urlDecodeFormComponent

Description: Decodes a URL form component encoded per RFC-1866 (decodes + to a space).

Syntax:

urlDecodeFormComponent(url: String) → String

Parameters:

• url: URL-encoded string.

Returns:

The decoded string or null if the input is invalid.

Example:

SELECT urlDecodeFormComponent('Hello+World%21');
// Returns: "Hello World!"

urlNetloc

Description: Extracts the network locality from the URL. This includes user information (if any), the host, and the port.

Syntax:

urlNetloc(url: String) → String

Parameters:

• url: URL string.

Returns:

A string in the format username:password@host:port (if user info is present) or null if the URL is invalid.

Example:

SELECT urlNetloc('https://user:pass@example.com:8080/path');
// Returns: "user:pass@example.com:8080"

cutWWW

Description: Removes the leading "www." from the URL’s domain.

Syntax:

cutWWW(url: String) → String

Parameters:

• url: URL string.

Returns:

The URL with the "www." prefix removed from the domain. If an error occurs, the original URL is returned.

Example:

SELECT cutWWW('https://www.example.com/path');
// Returns: "https://example.com/path"

cutQueryString

Description: Removes the query string (including the ?) from the URL.

Syntax:

cutQueryString(url: String) → String

Parameters:

• url: URL string.

Returns:

The URL without the query string. If an error occurs, the original URL is returned.

Example:

SELECT cutQueryString('https://example.com/path?key=value');
// Returns: "https://example.com/path"

cutFragment

Description: Removes the fragment identifier (including the #) from the URL.

Syntax:

cutFragment(url: String) → String

Parameters:

• url: URL string.

Returns:

The URL without the fragment. If an error occurs, the original URL is returned.

Example:

SELECT cutFragment('https://example.com/path#section');
// Returns: "https://example.com/path"

cutQueryStringAndFragment

Description: Removes both the query string and the fragment identifier from the URL.

Syntax:

cutQueryStringAndFragment(url: String) → String

Parameters:

• url: URL string.

Returns:

The URL stripped of both the query string and fragment. If an error occurs, the original URL is returned.

Example:

SELECT cutQueryStringAndFragment('https://example.com/path?key=value#section');
// Returns: "https://example.com/path"

cutURLParameter

Description: Removes a specific query parameter from the URL.

Syntax:

cutURLParameter(url: String, name: String) → String

Parameters:

• url: URL string.

• name: The query parameter name to remove.

Returns:

The URL with the specified query parameter removed. If an error occurs, the original URL is returned.

Example:

SELECT cutURLParameter('https://example.com/path?key=value&remove=this', 'remove');
// Returns: "https://example.com/path?key=value"

cutURLParameters

Description: Removes multiple query parameters from the URL.

Syntax:

cutURLParameters(url: String, names: String[]) → String

Parameters:

• url: URL string.

• names: An array of query parameter names to remove.

Returns:

The URL with the specified query parameters removed.

Example:

SELECT cutURLParameters('https://example.com/path?key=value&remove=this&other=param', ARRAY['remove', 'other']);
// Returns: "https://example.com/path?key=value"
PreviousUser-Defined Functions (UDFs)NextUnique Count and cardinality Estimation Functions

Last updated 3 months ago

Was this helpful?