Skip to content

Product Documentation

Main NavigationUser Guide SQL Reference Yellowbrick

Appearance

Sidebar Navigation

Getting Started with Yellowbrick

Provisioning the Data Warehouse

Data Warehouse Instances

Shared Services Type

Reserved Node Capacity

Suspending and Resuming Instances

Virtual Compute Clusters

Databases and Data Storage

Users and Roles

Queries and Administrative Tasks

Tutorial: Creating and Using Clusters

Create Two Compute Clusters

Create a New Role and Some Users

Load with the Small Cluster

Query with the Large Cluster

SQL-Based Loads from External Storage

Bulk Loads

Summary of LOAD TABLE Steps

Loading a Table via the Load Assistant

Connecting Tools and Applications

Client Tools and Drivers

Installing the Java Runtime Environment

Installing ybtools

Installing ybtools on Linux

Installing ybtools on AIX

Installing ybtools on macOS

Installing ybtools on Windows

Installing the "generic" ybtools

Uninstalling ybtools

Opening Network Ports for Clients

Downloading Drivers

Using the ADO.NET Driver

Setting Up a Database Connection

Common Options in ybtools

Object Storage Options

Common Environment Variables in ybtools

Examples of OAuth 2.0/OIDC Connections

Configuring SSL/TLS for Tools and Drivers

SSL/TLS Overview

Verifying SSL/TLS Encryption

Secure Connections for ODBC/JDBC Clients and ybsql

Secure Connections for ODBC Clients

Secure Connections for JDBC Clients

Secure Connections for ybsql (libpq)

Secure Connections for DBeaver Clients

Secure Connections for Java-based ybtools

Obtaining a Certificate Chain

Troubleshooting SSL Issues

ybsql Reference

ybsql Connections

ybsql Environment Variables

ybsql Startup File

ybsql Command-Line Options

ybsql Slash (\) Commands

ybsql \copy Command

\copy Parameters

\copy Examples

ybsql Examples

ybsql Properties and Variables

ybsql Properties

ybsql Display Properties

ybsql User Variables

ybsql Prompt Variables

Examples with ybsql Variables

Database Administration

Database Objects

Database Limits

Managing Database Users and Roles

Creating Databases

Managing Tables and Views

Distributing Data

Partitioning Tables

Auto-Analyzing Tables

Generating Values with Sequences

System Columns in Tables

Disk Quotas

Managing the Row Store

Generating DDL

Running Queries

Cross-Database Queries

Canceling Queries

Managing Idle Sessions

Encrypting Sensitive Data

SQL Encryption Examples

Encryption Key Rotation

Deprecated Encryption Functions

Creating Stored Procedures

Checking CDWM and Instance Versions

System Views

sys.backup

sys.backup_chain

sys.backup_snapshot

sys.cluster

sys.cluster_status

sys.column

sys.const

sys.database

sys.external_format

sys.external_location

sys.external_storage

sys.hardware_instance_assignment

sys.hardware_instance_limits

sys.hardware_instance_type

sys.hardware_instance_usage

sys.instance_id

sys.key

sys.lock

sys.load

sys.log_analyze

sys.log_authentication

sys.log_backup

sys.log_cluster_event

sys.log_load

sys.log_query

sys.log_query_alert

sys.log_query_analyze

sys.log_query_explain

sys.log_replica_status

sys.log_restore

sys.log_session

sys.log_unload

sys.procedure

sys.query

sys.query_analyze

sys.query_explain

sys.query_rule_event

sys.remote_server

sys.replica

sys.replica_status

sys.restore

sys.role

sys.schema

sys.sequence

sys.session

sys.storage

sys.table

sys.table_flush_summary

sys.table_info

sys.table_partition

sys.table_storage

sys.unload

sys.user

sys.view

sys.wlm_active_pool

sys.wlm_active_profile

sys.wlm_active_rule

sys.wlm_pending_rule

sys.wlm_pending_pool

sys.worker

Loading Tables

Bulk Loading Tables

Running a Bulk Load

Using the Dry Run Option

Setting the Commit Interval

Saving Load Options to a File

Loading Generated Key Values

Specifying NULL Behavior for Different Columns

Deletes, Updates, and Upserts

Handling Duplicate Rows

Monitoring Load Operations

Analyzing Rejected Rows

Resuming a Partial Load

Loading Tables from Parquet Files

Parquet Schema Mapping and Type Casting

Parquet Date and Time Conversion

Parquet Load Examples

Loading Data Exported from SQL Server

ybload Command

ybload Options

Setting --format for Flat Files

NULL and Empty Markers

Character Escape Sequences

ybload Field Options

ybload Date Formats

ybload Time Formats

ybload Timestamp Formats

ybload Advanced Processing Options

Escaping Quotes in Windows Clients

Bulk Load Examples

Bulk Load Insert Examples

Delete, Update, and Upsert Examples

Examples with Duplicate Rows

Loading Data from Object Storage

Loading from Amazon S3

S3 Authentication Methods

S3 Object Storage URIs

S3 Object Storage Examples

Loading from S3-Compatible Object Storage

Loading from Azure Blob Storage

Azure Authentication Methods

Azure Blob Storage URIs

Azure Blob Storage Examples

Trickle Loading Data via JDBC

Performance Factors

Performance Tuning

Example of a Trickle Load Java Program

Loading Tables with Spark

Setting Up the ybrelay Service

Running the ybrelay-init Script

TLS Support for ybrelay and Spark

Creating a Java Keystore

Starting the ybrelay Service Manually

Setting up and Running a Spark Job

Spark Application Options

Spark Format for Reading Schemas

Error Tolerance

Examples of Spark Jobs

Output for a Spark Job

ybrelay Options

Spark and ybrelay Glossary

Unloading Data

Setting Up an Unload

ybunload Command

ybunload Options

ybunload Output Files

ybunload Examples

Saving Unload Options to a File

Unloading Data to Parquet Files

Parquet Processing Options

Data Type Mapping for Parquet Unloads

Unloading Data to Object Storage

Unloading Data to an S3 Bucket

Unloading Data to Azure Storage

Workload Management

Sharing System Resources

How Queries Are Executed

How WLM Works

Creating WLM Profiles

Creating WLM Resource Pools

Allocating Query Memory

Examples of Flex Pools

Creating WLM Rules

Rule Types

Rule Conditions

Rule Actions

Rule Processing

Rule Examples

Submit Rule Examples

Assemble Rule Examples

Compile Rule Examples

Runtime Rule Examples

Completion Rule Examples

Restart Rule Examples

Examples of JavaScript for Rules

JavaScript Properties for WLM Rules

Recoverable Error Codes

Throttling Concurrency

Moving Queries

Restarting Queries

Using Query Tags

A Simple WLM Example

Backup and Restore

Overview

Backup Strategies

Restore Operations

Backup and Restore Clients

Backup and Restore Glossary

ybbackup Commands

ybbackup Options

ybbackup Examples

ybrestore Commands

ybrestore Options

ybrestore Examples

Running Incremental Restores

Running a Single-Table Restore

Restoring Users and Roles

ybbackupctl Commands

ybbackupctl Options

ybbackupctl Examples

Decrypting Columns in a Restored Database

HOT_STANDBY and READONLY Modes

Database Replication

Overview

Setting Up Replication

Setting Up Loopback Replication

Configuring SSL Trust

Seeding a Replica

Monitoring Replication

Managing Replication

Pausing and Resuming Replication

Dropping a Replica

Replication Failover and Failback

Rolling Back a Replicated Database

Replication SQL Commands

Replication Glossary

SQL Reference

SQL Data Types

BIGINT

BOOLEAN

CHAR

DATE

DECIMAL

Calculations with DECIMAL Values

Examples of DECIMAL Calculations

DOUBLE PRECISION

INTEGER

IPV4

IPV6

MACADDR

MACADDR8

REAL

SMALLINT

TIME

TIMESTAMP

TIMESTAMP WITH TIME ZONE

UUID

VARCHAR

Trailing Blanks in Character Data

Data Type Casting

Explicit Casting

Implicit Casting

Implicit Casting Examples

SQL String Constants

C-style Escape Sequences in String Constants

Unicode Escape Sequences in String Constants

Escape Sequences for Non-Printable Characters

SQL Commands

SQL Identifiers

ABORT

ALTER CLUSTER

ALTER DATABASE

ALTER DATABASE ADD REPLICA

ALTER DATABASE ALTER REPLICA

ALTER DATABASE DROP REPLICA

ALTER DEFAULT PRIVILEGES

ALTER EXTERNAL FORMAT

ALTER EXTERNAL LOCATION

ALTER EXTERNAL STORAGE

ALTER PROCEDURE

ALTER REMOTE SERVER

ALTER ROLE

ALTER SCHEMA

ALTER SEQUENCE

ALTER SYSTEM

ALTER SYSTEM SET CLUSTER

ALTER TABLE

ALTER USER

ALTER USER SET DEFAULT_CLUSTER

ALTER VIEW

ALTER WLM PROFILE

ALTER WLM RESOURCE POOL

ALTER WLM RULE

BEGIN

CALL

CANCEL

CLOSE

COMMENT ON

COMMIT

COPY

CREATE CLUSTER

CREATE DATABASE

CREATE EXTERNAL FORMAT

External Format Load Options

CREATE EXTERNAL LOCATION

CREATE EXTERNAL STORAGE

CREATE KEY

CREATE PROCEDURE

CREATE REMOTE SERVER

CREATE ROLE

CREATE SCHEMA

CREATE SEQUENCE

CREATE TABLE

CREATE TABLE Examples

Distribution Options

Partitioning Options

Sorted and Clustered Tables

CREATE TABLE AS

CREATE USER

CREATE VIEW

CREATE WLM PROFILE

CREATE WLM RESOURCE POOL

CREATE WLM RULE

DEALLOCATE

DECLARE

DELETE

DESCRIBE

DROP BACKUP CHAIN

DROP CLUSTER

DROP DATABASE

DROP EXTERNAL FORMAT

DROP EXTERNAL LOCATION

DROP EXTERNAL STORAGE

DROP KEY

DROP OWNED BY

DROP PROCEDURE

DROP REMOTE SERVER

DROP ROLE

DROP SCHEMA

DROP SEQUENCE

DROP TABLE

DROP USER

DROP VIEW

DROP WLM PROFILE

DROP WLM RESOURCE POOL

DROP WLM RULE

END

EXECUTE

EXPLAIN

FETCH

GRANT

ON CLUSTER

ON DATABASE

ON EXTERNAL object

ON KEY

ON PROCEDURE

ON ROLE

ON SCHEMA

ON SEQUENCE

ON SYSTEM

ON TABLE

ROLE

Abbreviations for ACLs

HELP

IMPORT SSL TRUST

INSERT

LIST BUCKETS

LIST OBJECTS

LOAD TABLE

LOCK SEQUENCE

MOVE cursor

MOVE query

PREPARE

REASSIGN OWNED BY

RELEASE SAVEPOINT

RESET

RESTART query

REVOKE

REVOKE SSL TRUST

ROLLBACK

ROLLBACK DATABASE TO SNAPSHOT

ROLLBACK TO SAVEPOINT

SAVEPOINT

SELECT

SETTING Clause

WITH Clause

SELECT List

FROM Clause

WHERE Clause

GROUP BY Clause

GROUP BY GROUPING SETS

GROUP BY ROLLUP

GROUP BY CUBE

HAVING Clause

WINDOW Clause

ORDER BY Clause

LIMIT Clause

OFFSET Clause

UNION, INTERSECT, EXCEPT

Subqueries

Subquery Examples

Correlated Subqueries

SELECT INTO

SET

SET ROLE

SET SESSION AUTHORIZATION

SHOW

Configuration Parameters

SHOW SSL CA

SHOW SSL IDENTITY

SHOW SSL SYSTEM

SHOW SSL TRUST

START TRANSACTION

SUSPEND INSTANCE

TABLE

TRUNCATE

UPDATE

USE CLUSTER

SQL Functions and Operators

Supported Functions and Return Types

SQL Conditions

Logical Operators

Comparison Operators

ANY

BETWEEN

EXISTS

IN

String Functions

ASCII

BIT_LENGTH

BTRIM

CHAR_LENGTH

CHR

CONCAT, ||

CONCAT_WS

DECRYPT (deprecated)

DECRYPT_KS

ENCODE

ENCRYPT (deprecated)

ENCRYPT_KS

ENCRYPT_KS Examples

Encryption and Decryption Algorithms

HASH

HASH4

HASH8

HMAC

HMAC_KS

INITCAP

INSTR

LEFT

LENGTH

LOWER

LPAD

LTRIM, RTRIM

MD5

NYSIIS

OCTET_LENGTH

POSITION

REPEAT

REPLACE

REVERSE

RIGHT

RPAD

SPLIT_PART

STRPOS

SUBSTR

SUBSTRING

TO_ASCII

TO_HEX

TRANSLATE

TRIM

UPPER

Pattern Matching

SQL Operators and Pattern Matching Functions

SQL Regex Operators

REGEXP_REPLACE

REGEXP_INSTR

REGEXP_EXTRACT

REGEXP_LIKE

LIKE

SIMILAR TO

SIMILAR TO: Best Practices

SUBSTRING (Pattern Match)

Simple Regex Examples

Regular Expression Details

Character Classes and Escaped Special Characters

Quantifiers

Anchors

Grouping Constructs

Alternation Constructs

Back References

Flags

Datetime Functions

ADD_MONTHS

AGE

AT TIME ZONE

CURRENT_DATE

CURRENT_TIMESTAMP

CURRENT_UTC_TIMESTAMP()

DATEADD

DATEDIFF

DATENAME

DATE_PART

DATE_TRUNC

DAY

DAYS_BETWEEN

EOMONTH

EXTRACT

GETDATE

JUSTIFY_DAYS, _HOURS, _INTERVAL

LAST_DAY

LOCALTIME

LOCALTIMESTAMP

MAKE_DATE

MAKE_INTERVAL

MAKE_TIME

MAKE_TIMESTAMP

MAKE_TIMESTAMPTZ

MONTH

MONTHS_BETWEEN

NEXT_DAY

NOW()

OVERLAPS

TIMEOFDAY()

TIMEZONE

YEAR

YEARS_BETWEEN

Supported Date Parts

Mathematical Functions

Mathematical Operators

ABS

ACOS

ASIN

CBRT

CEIL

COS

DEGREES

DIV

ERF

EXP

FLOOR

INV_NORM

LN

LOG

MOD

POWER

PROBNORM

RADIANS

RANDOM()

ROUND

ROUND_VAR

SIGN

SIN

SQRT

TRUNC

Aggregate Functions

AVG

COUNT

COUNT(DISTINCT)

GROUP_CONCAT

GROUPING

LISTAGG

MAX

MEDIAN()

MIN

PERCENTILE_CONT

PERCENTILE_DISC

STDDEV_SAMP and STDDEV_POP

STRING_AGG

SUM

VAR_SAMP and VAR_POP

Window Functions

Syntax for Window Functions

AVG (window function)

COUNT (window function)

CUME_DIST

DENSE_RANK

FIRST_VALUE, LAST_VALUE

LEAD, LAG

MAX (window function)

MEDIAN (window function)

MIN (window function)

NTILE

PERCENT_RANK

PERCENTILE_CONT (window function)

PERCENTILE_DISC (window function)

RANK

ROW_NUMBER

STDDEV_SAMP and STDDEV_POP (window functions)

SUM (window function)

VAR_SAMP and VAR_POP (window functions)

Conditional Expressions

CASE

COALESCE

DECODE

GREATEST and LEAST

IIF

ISNULL

NULLIF

NVL

NVL2

Formatting Functions

FLOAT4, FLOAT8

INT2, INT4, INT8

TO_CHAR

TO_DATE

TO_NUMBER

TO_TIMESTAMP

Formats for Datetime Values

Formats for Numeric Values

Type-Safe Casting Functions

SAFE_TO_DATE

SAFE_TO_DOUBLE

SAFE_TO_TIMESTAMP

JSON Functions

JSON_ARRAY_STR

JSON_INGEST

JSON_LOOKUP

JSON_OBJECT_STR

CONVERT (SQL Server Migration Function)

GETBIT Function

NEXTVAL Function

System Functions

CURRENT_CLUSTER()

CURRENT_DATABASE()

CURRENT_QUERY()

CURRENT_SCHEMA

CURRENT_SCHEMAS()

CURRENT_SETTING

CURRENT_USER

DEFAULT_CLUSTER()

GETDATABASEENCODING()

HAS_CLUSTER_PRIVILEGE

HAS_DATABASE_PRIVILEGE

HAS_EXTERNAL_*_PRIVILEGE

HAS_FUNCTION_PRIVILEGE

HAS_KEY_PRIVILEGE

HAS_ROLE_PRIVILEGE

HAS_SCHEMA_PRIVILEGE

HAS_SYSTEM_PRIVILEGE

HAS_TABLE_PRIVILEGE

PG_ADVISORY_LOCK

PG_ADVISORY_UNLOCK

PG_HAS_ROLE

PG_RELOAD_CONF()

PG_TRY_ADVISORY_LOCK

SESSION_USER

SYSTEM_CLUSTER()

sys.execution_id()

sys.filesys_number_from_rowid

sys.gen_random_uuid()

sys.inode_number_from_rowid

sys.oldest_rollback_point_id_in_replica

sys.row_number_from_rowid

sys.worker_id()

sys.worker_id_from_rowid

sys.worker_uuid

VERSION()

yb_get_view_table_usage()

yb_terminate_session

Network Address Functions

CONTAINS

HOSTMASK

INET_MERGE

IPV4_GET_INET, IPV6_GET_INET

IPV4_GET_MASKBITS, IPV6_GET_MASKBITS

IPV4_NETMASK, IPV6_NETMASK

MACADDR8_SET7BIT

TEXT

TRUNC

SQL Reserved Words

Error Codes

Glossary

Third-Party Licenses

Change History

Release 6.6

Release 6.5

Release 6.4

Release 6.3

On this page

Table of Contents for current page

Yellowbrick Data Warehouse Version 6

Change History

Release 6.6
Release 6.5
Release 6.4
Release 6.3

Prior Releases

For information on releases prior to those listed above, please contact Yellowbrick Support

Previous pageThird-Party Licenses

Next pageRelease 6.6

© 2018 - 2023 Yellowbrick Data, Inc. All Rights Reserved. | Terms and Privacy Policy