Skip to content

Product Documentation

Main NavigationUser Guide and SQL Reference Yellowbrick

Appearance

Sidebar Navigation

System Management

External Interfaces

System Maintenance

System Hardware Encryption

Setting Up Encryption

Example: Set Up the Keystore and Enable Encryption

Example: Use Multiple Unlock Keys

Example: Force Keystore Setup

Managing Encrypted Drives

Example: Rotate the Keystore with Multiple Unlock Keys

Manager Node Failover

Starting and Stopping the Database

Powering the Appliance Off and On

Checking the Logs

User Accounts

Using the System Management Console

Console Login

Introduction to the SMC

Configuration Settings in the SMC

Chassis Management

System Alerts

Alert Types

Creating an Alert Endpoint

Endpoint Types

Alert Templates

Managing Alerts

Remote Diagnostics

Diagnostics Service Levels

Enabling Remote Diagnostics

Configuring Remote Diagnostics

Using Network Tracing

Manager Node Ports

ybcli Reference

Running ybcli in script mode

blade

clear

config

config bmc

config dba

config firewall

config hostname

config keystore

config network

config ntp

config phonehome

config timezone

config user

database

encryption

exit, quit

health

help

keystore

log

manager

status

system

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

Uninstalling ybtools

Opening Network Ports for Clients

SSL/TLS Settings For ybtools

Downloading Drivers

Using the ADO.NET Driver

Connecting AWS to Yellowbrick

Accepting the Network Interface

Configuring Network Routing

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

Creating Databases

Managing Tables and Views

Distributing Data

Partitioning Tables

Auto-Analyzing Tables

External Tables

Generating Values with Sequences

System Columns in Tables

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

Managing Database Users

Enabling SSL/TLS Encryption

LDAP Integration

Importing Certificates

LDAP Sample Schema

LDAP Authentication

LDAP Authentication Modes

Setting Up Authentication

LDAP Authentication Settings

Bind Examples

Search, then Bind Example

Synchronizing Users and Groups

LDAP Synchronization Settings

LDAP Synchronization: Basic Example

LDAP Synchronization: Examples with Filters

Checking the Server Version

Bulk Loading Tables

Running a Bulk Load

Using the Dry Run Option

Setting the Commit Interval

Saving Load Options to a File

Specifying NULL Behavior for Different Columns

Loading Data Exported from SQL Server

Bulk Load Examples

Loading Tables 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

ybload Object Storage Options

ybload Command

ybload Options

Setting the --format Option

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

Monitoring Load Operations

Analyzing Rejected Rows

Resuming a Partial Load

Trickle Loading Data via JDBC

Performance Factors

Performance Tuning

Example of a Trickle Load Java Program

Loading Tables with Spark

Setting Up ybrelay Service

Setting Up Spark

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

Saving Unload Options to a File

Unloading Data to an S3 Bucket

ybunload Command

ybunload Options

ybunload Output Files

ybunload Examples

Workload Management

Introduction to WLM

How WLM Works

Creating Profiles

Creating Resource Pools

Allocating Query Memory

Example: Memory Allocation Among Resource Pools

Examples of Flex Pools

Moving a Query to Another Pool

Creating Rules

Rule Types

Rule Conditions

Rule Actions

Throttling Concurrency

Query Tags

Quick Rules in the SMC

Examples of JavaScript for Rules

JavaScript Properties

Exporting Profiles

Monitoring WLM Activity

A Simple WLM Example

WLM Example: Using the SMC

WLM Example: Using SQL

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

Restoring Users and Roles

ybbackupctl Commands

ybbackupctl Options

ybbackupctl Examples

Monitoring Backup and Restore Operations

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

System Views

sys.alert

sys.backup

sys.backup_chain

sys.backup_legacy

sys.backup_snapshot

sys.blade

sys.column

sys.const

sys.database

sys.drive_summary

sys.key

sys.lock

sys.load

sys.log_alert

sys.log_analyze

sys.log_audit

sys.log_authentication

sys.log_backup

sys.log_backup_legacy

sys.log_error

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_restore_legacy

sys.log_session

sys.log_unload

sys.log_warning

sys.mount

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.restore_legacy

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

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

Type Casting

Coercion Notes

SQL Identifiers

SQL String Constants

C-style Escape Sequences in String Constants

Unicode Escape Sequences in String Constants

Escape Sequences for Non-Printable Characters

SQL Commands

ABORT

ALTER DATABASE

ALTER DATABASE ADD REPLICA

ALTER DATABASE ALTER REPLICA

ALTER DATABASE DROP REPLICA

ALTER DEFAULT PRIVILEGES

ALTER PROCEDURE

ALTER REMOTE SERVER

ALTER ROLE

ALTER SCHEMA

ALTER SEQUENCE

ALTER SYSTEM

ALTER TABLE

ALTER USER

ALTER VIEW

ALTER WLM PROFILE

ALTER WLM RESOURCE POOL

ALTER WLM RULE

BEGIN

CALL

CANCEL

CLOSE

COMMENT ON

COMMIT

CREATE DATABASE

CREATE EXTERNAL MOUNT

CREATE EXTERNAL TABLE

USING Options

External Table Examples

CREATE KEY

CREATE PROCEDURE

CREATE REMOTE SERVER

CREATE ROLE

CREATE SEQUENCE

CREATE SCHEMA

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 DATABASE

DROP EXTERNAL MOUNT

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 DATABASE

ON KEY

ON PROCEDURE

ON SCHEMA

ON SEQUENCE

ON SYSTEM

ON TABLE

ROLE

HELP

IMPORT SSL TRUST

INSERT

LOCK SEQUENCE

MOVE cursor

MOVE query

PREPARE

REASSIGN OWNED BY

RELEASE SAVEPOINT

RESET

REVOKE

REVOKE SSL TRUST

ROLLBACK

ROLLBACK DATABASE

ROLLBACK TO SAVEPOINT

SAVEPOINT

SELECT

WITH Clause

SELECT List

FROM Clause

WHERE Clause

GROUP BY Clause

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

SHOW SSL CA

SHOW SSL IDENTITY

SHOW SSL SYSTEM

SHOW SSL TRUST

START TRANSACTION

TABLE

TRUNCATE

UPDATE

SQL Functions and Operators

Supported Functions and Return Types

SQL Conditions

Logical Operators

Comparison Operators

ANY

BETWEEN

EXISTS

IN

String Functions

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

DATE_PART

DATE_TRUNC

DAY

DAYS_BETWEEN

EOMONTH

EXTRACT

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

Supported Dateparts

Mathematical Functions

Mathematical Operators

ABS

ACOS

ASIN

CBRT

CEIL

COS

DEGREES

DIV

ERF

EXP

FLOOR

LN

LOG

MOD

POWER

PROBNORM

RADIANS

RANDOM()

ROUND

SIGN

SIN

SQRT

TRUNC

Aggregate Functions

AVG

COUNT

COUNT(DISTINCT)

GROUP_CONCAT

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

ISNULL

NULLIF

NVL

NVL2

Formatting Functions

FLOAT4, FLOAT8

INT2, INT4, INT8

TO_CHAR

TO_DATE

TO_TIMESTAMP

Formats for Datetime Values

JSON Functions

JSON_ARRAY_STR

JSON_OBJECT_STR

GETBIT Function

NEXTVAL Function

System Functions

CURRENT_DATABASE()

CURRENT_QUERY()

CURRENT_SCHEMA

CURRENT_USER

HAS_DATABASE_PRIVILEGE

HAS_FUNCTION_PRIVILEGE

HAS_SCHEMA_PRIVILEGE

HAS_SYSTEM_PRIVILEGE

HAS_TABLE_PRIVILEGE

PG_HAS_ROLE

SESSION_USER

sys.execution_id()

sys.filesys_number_from_rowid

sys.gen_random_uuid()

sys.inode_number_from_rowid

sys.ldap_sync

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_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

Notices and Licenses

Third Party Licenses

OpenSSL License

Third Party Library Notices

Error Codes

Glossary

Change History

On this page

Table of Contents for current page

JSON Functions

This section covers functions that are used to compose JSON objects and arrays.

In This Section

JSON_ARRAY_STR
JSON_OBJECT_STR

Parent topic:SQL Functions and Operators

Previous pageFormats for Datetime Values

Next pageJSON_ARRAY_STR

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