Yellowbrick Data Warehouse

https://docs.yellowbrick.com/
Admin
Yellowbrick Data Warehouse is a high-performance, columnar SQL database designed for analytics and...
Tokens:845,422
Snippets:7,850
Trust Score:10
Update:6 months ago
Show doc for...
Tokens:
Raw
### Example Installation Output

Source: https://docs.yellowbrick.com/7.4.3/client_tools/installing_clients_linux.html

Sample terminal output showing the installation process on an Ubuntu system.

```text
$ sudo apt-get install ./ybtools-6.1.12-7a4d6a43.3360.ubuntu.20.04.x86_64.deb
Reading package lists... Done
Building dependency tree
Reading state information... Done
Note, selecting 'ybtools' instead of './ybtools-6.1.12-7a4d6a43.3360.ubuntu.20.04.x86_64.deb'
The following packages were automatically installed and are no longer required:
  bsh libasm3-java libganymed-ssh2-java libisl13 libjackrabbit-java libjline-java libjna-java libjna-jni libjna-platform-java libjsch-agent-proxy-java
  libjsoncpp0v5 liblldb-3.8 libllvm3.8 libmaven-scm-java libnetbeans-cvsclient-java libruby2.2 libsisu-guice-java llvm-3.8 llvm-3.8-dev llvm-3.8-runtime
  nagios-plugins-common python3-bcdoc ruby2.1-dev
Use 'sudo apt autoremove' to remove them.
The following packages will be upgraded:
  ybtools
1 upgraded, 0 newly installed, 0 to remove and 346 not upgraded.
Need to get 0 B/185 MB of archives.
After this operation, 0 B of additional disk space will be used.
Get:1 /home/brumsby/ybtools-6.1.12-7a4d6a43.3360.ubuntu.20.04.x86_64.deb ybtools amd64 6.1.12-7a4d6a43.3360 [185 MB]
(Reading database ... 229176 files and directories currently installed.)
Preparing to unpack .../ybtools-6.1.12-7a4d6a43.3360.ubuntu.20.04.x86_64.deb ...
Unpacking ybtools (6.1.12-7a4d6a43.3360) over (5.4.0-20220620182315) ...
Setting up ybtools (6.1.12-7a4d6a43.3360) ...
update-alternatives: setting up automatic selection of ybbackup
update-alternatives: using /opt/ybtools/bin/ybbackup to provide /usr/bin/ybbackup (ybbackup) in auto mode
update-alternatives: setting up automatic selection of ybbackupctl
update-alternatives: using /opt/ybtools/bin/ybbackupctl to provide /usr/bin/ybbackupctl (ybbackupctl) in auto mode
update-alternatives: setting up automatic selection of ybdumproles
update-alternatives: using /opt/ybtools/bin/ybdumproles to provide /usr/bin/ybdumproles (ybdumproles) in auto mode
update-alternatives: setting up automatic selection of ybdumpschema
update-alternatives: using /opt/ybtools/bin/ybdumpschema to provide /usr/bin/ybdumpschema (ybdumpschema) in auto mode
update-alternatives: setting up automatic selection of ybload
update-alternatives: using /opt/ybtools/bin/ybload to provide /usr/bin/ybload (ybload) in auto mode
update-alternatives: setting up automatic selection of ybrelay
update-alternatives: using /opt/ybtools/bin/ybrelay to provide /usr/bin/ybrelay (ybrelay) in auto mode
update-alternatives: setting up automatic selection of ybrestore
update-alternatives: using /opt/ybtools/bin/ybrestore to provide /usr/bin/ybrestore (ybrestore) in auto mode
update-alternatives: setting up automatic selection of ybsql
update-alternatives: using /opt/ybtools/bin/ybsql to provide /usr/bin/ybsql (ybsql) in auto mode
update-alternatives: setting up automatic selection of ybtoken
update-alternatives: using /opt/ybtools/bin/ybtoken to provide /usr/bin/ybtoken (ybtoken) in auto mode
update-alternatives: setting up automatic selection of ybunload
update-alternatives: using /opt/ybtools/bin/ybunload to provide /usr/bin/ybunload (ybunload) in auto mode
update-alternatives: setting up automatic selection of ybrelay-init
update-alternatives: using /opt/ybtools/config/ybrelay-init to provide /usr/bin/ybrelay-init (ybrelay-init) in auto mode
```

--------------------------------

### Example Installation Output

Source: https://docs.yellowbrick.com/7.4.3/client_tools/installing_clients_aix.html

Sample output showing the dependency resolution process during ybtools installation.

```bash
$ yum install ybtools-6.1.2-a8363b32.1234.aix7.1.ppc.rpm
Failed to set locale, defaulting to C
AIX_Toolbox                                                                                                      | 2.9 kB  00:00:00
AIX_Toolbox/primary_db                                                                                           | 1.4 MB  00:00:00
AIX_Toolbox_71                                                                                                   | 2.9 kB  00:00:00
AIX_Toolbox_71/primary_db                                                                                        |  23 kB  00:00:00
AIX_Toolbox_noarch                                                                                               | 2.9 kB  00:00:00
AIX_Toolbox_noarch/primary_db                                                                                    |  62 kB  00:00:00
Setting up Install Process
Examining ybtools-6.1.2-a8363b32.1234.aix7.1.ppc.rpm: ybtools-6.1.2-a8363b32.1234.aix7.1.ppc
Marking ybtools-6.1.2-a8363b32.1234.aix7.1.ppc.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package ybtools.ppc 0:6.1.2-a8363b32.1234 will be installed
--> Processing Dependency: libldap.a(libldap-2.4.so.2) for package: ybtools-6.1.2-a8363b32.1234.aix7.1.ppc
--> Running transaction check
---> Package openldap.ppc 0:2.4.48-1 will be installed
--> Processing Dependency: cyrus-sasl >= 2.1.26-2 for package: openldap-2.4.48-1.ppc
--> Processing Dependency: libgcc >= 6.3.0-1 for package: openldap-2.4.48-1.ppc
--> Processing Dependency: libsasl2.a for package: openldap-2.4.48-1.ppc
--> Processing Dependency: libgcc_s.a(shr.o) for package: openldap-2.4.48-1.ppc
--> Running transaction check
---> Package cyrus-sasl.ppc 0:2.1.26-3 will be installed
---> Package libgcc.ppc 0:6.3.0-2 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
```

--------------------------------

### START AFTER Option Example

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/list_objects.html

An example of using the START AFTER option to specify an object name as a starting point for listing subsequent objects.

```sql
START AFTER 'premdb/newteam.csv'
```

--------------------------------

### Start ybsql with startup file

Source: https://docs.yellowbrick.com/7.4.3/administration/ybsqlrc_file.html

Example output showing ybsql session initialization with active startup settings.

```bash
$ ybsql premdb
Null display is "[NULL]".
Expanded display is used automatically.
ybsql (5.0.0)
Type:  \h for help with SQL commands
      \? for help with ybsql commands
      \g or terminate with semicolon to execute query
      \q to quit
premdb=#
```

--------------------------------

### Launch Interactive Web UI

Source: https://docs.yellowbrick.com/7.4.3/platforms/cloud/ee/cloud_install/cloud_deployer_cli_ref.html

Use the 'interactive' command to start the web application server for guided installation. Optionally specify a port and deployer access key.

```bash
./yb-install interactive [--port=<PORT> --key=<key>]
```

--------------------------------

### Force Keystore Setup Example

Source: https://docs.yellowbrick.com/7.4.3/platforms/appliance/encryption/example_force_setup.html

Use the 'keystore setup force' command to erase all drive keys and re-initialize the keystore. This action can lead to data loss if encryption is enabled and no backup is available. Always back up keys.

```bash
YBCLI (PRIMARY)> keystore setup force

You have entered the 'setup' command with the 'force' option, which will erase all drive keys in the keystore.
WARNING: Potential data loss. Please read the following information carefully.

 -> If encryption is not enabled on any of the drives, you are safe to proceed.
 -> If encryption is enabled, all data on the encrypted drives will be lost unless you can restore the keystore from a backup.
 -> If you cannot restore the keystore, each encrypted drive will have to be manually unlocked by using the key printed on its label. All data will be lost.

If you do not want to run the command with the force option, enter 'no' below.

Are you sure you want to do this?: yes
Forcing keystore initialization
Stopping keystore and reinitializing. Standby...  Done
Starting keystore. Standby...  Done
Setting up the keystore. Standby...
The system will generate two types of keys:
  -> A single authentication key
  -> One or more keys to unlock the keystore
At least one of each type of key is required.

Note: You can request up to 5 keystore unlock keys. In this way, multiple administrators can unlock the keystore
using a combination of keys. No single key has to be distributed to a single administrator.

How many unlock keys should be generated for the keystore? (1 to 5): 1
1 key will be required to unlock the keystore

Successfully initialized the keystore. Please store the keys listed below in a secure location.

The following 1 key is used to unlock the keystore after system bootup or failover:
Note: 1 of 1 key(s) are required for unlocking the keystore.
Keystore unlock key 1: 1c980bf78d027facb30e5ca2df401c1227d3292690a527566e24098955e02c07

The following key is used to authenticate to the keystore (required by any encryption command):
Authentication key: e8a96833-c8a8-065e-7927-e91f4f932e08

Keys have been generated. Please store them in a safe place.

Do you want to create a backup of the keystore?

Type yes to continue: yes

Stopping the keystore service before backup. Standby...  Done
Backing up keystore. Standby... Done
Starting the keystore service after backup. Standby...  Done

The keystore has been backed up successfully to:
	/tmp/ybd-ks-11-09-2019-19-48-56.tar.gz
Please copy the backup to another machine. The backup is located on this system at:
	yb00-mgr0.yellowbrick.io:/tmp/ybd-ks-11-09-2019-19-48-56.tar.gz
	MD5: 038e793f0571e32f34b4d554cf2cec66


Do you want to unlock the keystore (not required)?

Type yes to continue: no
Keystore will not be unlocked
```

--------------------------------

### Setup table for SRF join examples

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/set_returning_functions.html

Create and populate a table used to demonstrate SRF joins.

```sql
CREATE TABLE t (start INT, stop INT, step INT);

INSERT INTO t VALUES (3, 100, 29), (7, 8, 9), (3, 2, 1);
```

--------------------------------

### List Objects Starting After a Specific Key

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/list_objects.html

This example retrieves objects that begin with 'premdb/' and end with '.csv', starting the list after the object named 'premdb/newteam.csv'. It showcases the START AFTER option for pagination or resuming a previous query.

```sql
list objects external location premdbs3data
prefix('premdb/')
suffix('csv')
start after 'premdb/newteam.csv';

"object_name","size_bytes","creation_time","modify_time"
"premdb/player.csv",1286,,"2021-04-27T19:17:42-07:00"
"premdb/season.csv",746,,"2021-04-27T19:17:43-07:00"
```

--------------------------------

### Start ybrelay with work directory

Source: https://docs.yellowbrick.com/7.4.3/ybrelay_spark/ybrelay_options.html

Example command to start the ybrelay service while specifying the working directory.

```bash
$ ybrelay -w ./work
14:23:34.123 [ INFO] ABOUT CLIENT:
   app.cli_args         = "-w" "./work"
   app.name_and_version = "Yellowbrick Data Relay version 3.1.0-616"
   java.home            = "/usr/lib/jvm/java-11-oracle/jre"
   java.version         = "11"
   jvm.memory           = "491.00 MB (max=7.11 GB)"
   jvm.name_and_version = "Java HotSpot(TM) 64-Bit Server VM (build 25.101-b13, mixed mode)"
   jvm.options          = "-Xms512m, -Xmx8g, -ea, -Dapp.name=ybrelay, -Dapp.pid=11403, -Dapp.repo=/opt/ybtools/3.1.0.616/lib, -Dapp.home=/opt/ybtools/3.1.0.616, -Dbasedir=/opt/ybtools/3.1.0.616"
   jvm.vendor           = "Oracle Corporation"
   os.name_and_version  = "Linux 4.4.0-31-generic (amd64)"

14:23:34.125 [ INFO] Yellowbrick log level: DEBUG
...
```

--------------------------------

### Set and Get Application Name

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/current_setting.html

This example demonstrates setting the application_name configuration parameter and then retrieving its value using CURRENT_SETTING.

```sql
set application_name='YBSQL_CLIENT';
```

```sql
select current_setting('application_name');
```

--------------------------------

### System Chassis Setup

Source: https://docs.yellowbrick.com/7.4.3/platforms/appliance/ybcli/cli_system.html

Initiates the setup process for the system chassis.

```APIDOC
## POST /api/system/chassis/setup

### Description
Initiates the setup process for the system chassis.

### Method
POST

### Endpoint
/api/system/chassis/setup

### Response
#### Success Response (200)
- **message** (string) - Confirmation message that the chassis setup has started.

#### Response Example
```json
{
  "message": "Chassis setup process initiated."
}
```
```

--------------------------------

### Example Output of ybrelay-init

Source: https://docs.yellowbrick.com/7.4.3/ybrelay_spark/ybrelay_run_init_script.html

This output shows the informational messages and actions taken when initializing the ybrelay service, including group/user creation and service setup.

```text
INFO: Creating System Group: ybrelay with GID: 923
INFO: Creating System User: ybrelay with UID: 923
INFO: Creating Systemd service
INFO: JAVA_HOME environment variable detected
INFO: Adding JAVA_HOME configuration to ybrelay.config
Created symlink from /etc/systemd/system/multi-user.target.wants/ybrelay.service to /etc/systemd/system/ybrelay.service.
INFO: Starting Systemd service
```

--------------------------------

### Range Partition Limit Examples

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/partitioning_options.html

Examples demonstrating valid and invalid partition counts.

```SQL
between 1 and 1000000 each 1
```

```SQL
between 1 and 1000000 each 100
```

--------------------------------

### Verify Installation Directory

Source: https://docs.yellowbrick.com/7.4.3/client_tools/installing_clients_linux.html

Navigate to the default installation directory to verify the file structure.

```bash
$ cd /opt/ybtools
me@yb:/opt/ybtools$ ls
bin  config  integrations  lib  license.txt
```

--------------------------------

### Set Default Cluster Example

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/alter_system_default_cluster.html

Example of setting the default cluster for users.

```SQL
yellowbrick=# alter system set default_cluster "bobr-rc6-cluster1";
ALTER SYSTEM SET DEFAULT_CLUSTER
```

--------------------------------

### Get Yellowbrick Bundled Assets

Source: https://docs.yellowbrick.com/7.4.3/platforms/cloud/ee/cloud_install/cloud_deployer_cli_ref.html

Outputs a JSON object containing assets bundled by the Yellowbrick deployer. This is useful for self-managed installs to find the versions of Helm charts and Docker images. The example shows how to extract the cert-manager image tag.

```bash
./yb-install get-assets
```

```bash
./yb-install get-assets &> ~/manifest.json # /opt/ybd is readonly
cat ~/manifest.json | jq -r '.containerImages[] | select(.repo == "cert-manager") | .tag'
```

--------------------------------

### HELP Command Examples

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/help.html

Examples showing how to retrieve help for specific SQL commands like CANCEL and DROP VIEW.

```sql
yellowbrick=# help cancel;
 COMMAND |       DESCRIPTION       |     SYNTAX
---------+-------------------------+-----------------
 CANCEL  | Cancel a running query. | CANCEL query_id
(1 row)
yellowbrick=# help drop view;
  COMMAND  |         DESCRIPTION          |                           SYNTAX
-----------+------------------------------+-------------------------------------------------------------
 DROP VIEW | Drop a view from a database. | DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
(1 row)
```

--------------------------------

### Initialize Keystore and Generate Keys

Source: https://docs.yellowbrick.com/7.4.3/platforms/appliance/encryption/example_keystore_setup.html

Use this command to set up the keystore, generate authentication and unlock keys, and optionally back up the keystore. Follow the prompts to specify the number of unlock keys.

```bash
YBCLI (PRIMARY)> keystore setup

Setting up the keystore. Standby...
The system will generate two types of keys:
  -> A single authentication key
  -> One or more keys to unlock the keystore
At least one of each type of key is required.

Note: You can request up to 5 keystore unlock keys. In this way, multiple administrators can unlock the keystore
using a combination of keys. No single key has to be distributed to a single administrator.

How many unlock keys should be generated for the keystore? (1 to 5): 1
1 key will be required to unlock the keystore

Successfully initialized the keystore. Please store the keys listed below in a secure location.

The following key is used to authenticate to the keystore (required by any encryption command):
Authentication key: 5f2cb5e7-0e08-897e-1119-4ec98c8814a0

The following 1 key is used to unlock the keystore after system bootup or failover:
Note: 1 of 1 key(s) are required for unlocking the keystore.
Keystore unlock key 1: 5278a49d2180e1b2fa841eebc01a1ba9ad551e33ebef1f218fcef3db9f28ede6

Keys have been generated. Please store them in a safe place.

Do you want to create a backup of the keystore?

Type yes to continue: yes

Stopping the keystore service before backup. Standby...  Done
Backing up keystore. Standby... Done
Starting the keystore service after backup. Standby...  Done

The keystore has been backed up successfully to:
	/tmp/ybd-ks-11-03-2019-11-27-59.tar.gz
Please copy the backup to another machine. The backup is located on this system at:
	yb00-mgr0.yellowbrick.io:/tmp/ybd-ks-11-03-2019-11-27-59.tar.gz
	MD5: 272e2231990294380d9366a91b21e6e1


Do you want to unlock the keystore (not required)?

Type yes to continue: no
Keystore will not be unlocked
```

--------------------------------

### Get Next Monday's Date

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/next_day.html

This example demonstrates how to get the date of the next Monday following the current date.

```SQL
premdb=# select current_date, next_day(current_date,'Monday');
   date    |  next_day
------------+------------
 2017-11-20 | 2017-11-27
(1 row)
```

--------------------------------

### Clone the sample code repository

Source: https://docs.yellowbrick.com/7.4.3/tutorial/aws_lambda_ybload.html

Use Git to download the tutorial repository from GitHub.

```sql
git clone git@github.com:markcusack/yellowbrick-learn.git
```

--------------------------------

### Example: Retrieving Worker UUIDs

Source: https://docs.yellowbrick.com/7.4.3/administration/system_columns.html

This example demonstrates how to use `sys.worker_uuid` in conjunction with `sys.worker_id_from_rowid` to get the UUID for each compute node.

```APIDOC
## Example: Retrieving Worker UUIDs

To return the UUID for each compute node instead of its logical ID, use the `sys.worker_uuid` function:

```sql
yellowbrick_test=# select sys.worker_uuid(sys.worker_id_from_rowid(rowid)), count(*)
from crdm_giftcard group by sys.worker_id_from_rowid(rowid) order by sys.worker_id_from_rowid(rowid);
            worker_uuid              | count
--------------------------------------+-------
 00000000-0000-0000-0000-38b8ebd00154 | 24437
 00000000-0000-0000-0000-38b8ebd000dc | 24244
 00000000-0000-0000-0000-38b8ebd002c6 | 24761
 00000000-0000-0000-0000-38b8ebd00069 | 24361
 00000000-0000-0000-0000-38b8ebd00041 | 24000
 00000000-0000-0000-0000-38b8ebd00091 | 24348
 00000000-0000-0000-0000-38b8ebd000aa | 24497
 00000000-0000-0000-0000-38b8ebd003c5 | 24270
 00000000-0000-0000-0000-38b8ebd0009b | 24600
 00000000-0000-0000-0000-38b8ebd00997 | 24665
 00000000-0000-0000-0000-38b8ebd002bc | 24409
 00000000-0000-0000-0000-38b8ebd003a2 | 24359
 00000000-0000-0000-0000-38b8ebd00028 | 24528
 00000000-0000-0000-0000-38b8ebd00212 | 24176
 00000000-0000-0000-0000-38b8ebd0025d | 24590
(15 rows)
```
```

--------------------------------

### Prepare and Execute a Statement

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/deallocate.html

This example demonstrates preparing a statement with a parameter and then executing it.

```sql
premdb=# prepare x(varchar(30)) as select * from team where name=$1;
PREPARE
premdb=# execute x('Leicester City');
 teamid | atid | atid |      name      | nickname |   city    |      stadium       | capacity | avg_att
--------+------+------+----------------+----------+-----------+--------------------+----------+---------
    22 |   23 |   72 | Leicester City | Foxes    | Leicester | King Power Stadium |    32262 |  32.201
(1 row)
```

--------------------------------

### Initialize Keystore

Source: https://docs.yellowbrick.com/7.4.3/platforms/appliance/encryption/encryption_setup.html

Create a new keystore and generate authentication and unlock keys.

```bash
YBCLI (PRIMARY)> keystore setup

Setting up the keystore
...
```

--------------------------------

### Example: SIMILAR TO with Wildcard

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/similar_to.html

This example demonstrates using the SIMILAR TO operator to find rows where the 'ftscore' column starts with '8-'. The '%' wildcard matches any sequence of characters.

```sql
SELECT * FROM match
WHERE ftscore SIMILAR TO '8-%';
```

--------------------------------

### Setup Yellowbrick Repository

Source: https://docs.yellowbrick.com/7.4.3/learn/guides/spring-ai-vectorstore.html

Commands to clone, navigate, and build the Yellowbrick sample project.

```bash
git clone https://github.com/YellowbrickData/yellowbrick-learn
```

```bash
cd yellowbrick-learn/samples/rags/vectorstore
```

```bash
./mvnw install
```

--------------------------------

### Get non-self-signed certificate status

Source: https://docs.yellowbrick.com/7.4.3/platforms/appliance/ybcli/cli_config_cert.html

Example output when retrieving the status of a certificate that is not self-signed.

```bash
YBCLI(47552) (PRIMARY - yb97-mgr0)> config cert get

Certificate is not self-signed.
Subject's hostname is yb97.slc.yellowbrick.io
Issuer's hostname is ybinit_dev_ca
```

--------------------------------

### Start replication from the source system

Source: https://docs.yellowbrick.com/7.4.3/replication/replication_seeding.html

Initiate the replication process using the START WITH CHAIN syntax.

```sql
premdb=# alter database premdb alter replica premdb_replica
start with chain 'February2020';
START REPLICA
```

--------------------------------

### Get self-signed certificate status

Source: https://docs.yellowbrick.com/7.4.3/platforms/appliance/ybcli/cli_config_cert.html

Example output when retrieving the status of a self-signed certificate.

```bash
YBCLI(46888) (PRIMARY - yb97-mgr0)> config cert get

Current certificate's hostname is yb97.slc.yellowbrick.io
```

--------------------------------

### Retrieve manager node hostnames

Source: https://docs.yellowbrick.com/7.4.3/platforms/appliance/ybcli/cli_config_hostname.html

Example output for the config hostname get command.

```bash
YBCLI(13325) (PRIMARY - yb100-mgr0)> config hostname get

Local node : yb100-mgr0.ybroad.io
Remote node: yb100-mgr1.ybroad.io
```

--------------------------------

### Exclude Schemas with Wildcards

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/add_replica.html

When excluding schemas, you can use wildcard characters. This example excludes the 'public' schema, all schemas starting with 'premdb_', and all schemas starting with 'epl'. This is useful for managing replication of dynamically created schemas.

```sql
EXCLUDE 'public,premdb_*,epl*'
```

--------------------------------

### Initialize ybrelay with keystore and password prompt

Source: https://docs.yellowbrick.com/7.4.3/ybrelay_spark/ybrelay_options.html

Run the initialization utility to configure the service with a keystore and enable password prompting.

```bash
ybrelay-init --keystore ybkeystore.jks --prompt-password
```

--------------------------------

### Initial Load Configuration

Source: https://docs.yellowbrick.com/7.4.3/bulk_loading/ybload_advanced_processing.html

Recommended starting configuration for testing load performance.

```bash
--num-readers 1 --num-parsers-per-reader 2
```

--------------------------------

### Show All Configuration Parameters

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/show.html

Execute SHOW ALL to display a comprehensive list of all configuration parameters, their current settings, and a description for each. This is useful for understanding the system's configuration.

```sql
yellowbrick=# show all;
               name                 |             setting              |                                description
-------------------------------------+----------------------------------+----------------------------------------------------------------------------
 application_name                    | ybsql                            | Sets the application name to be reported in statistics and logs.
 block_size                          | 32768                            | Shows the size of a disk block.
 bytea_output                        | hex                              | Sets the output format for bytea.
 client_encoding                     | UTF8                             | Sets the client's character set encoding.
 client_min_messages                 | warning                          | Sets the message levels that are sent to the client.
 DateStyle                           | ISO, MDY                         | Sets the display format for date and time values.
 enable_alternative_round            | on                               | Translates round to round_var which allows for a non const second argument
 enable_silent_coerce                | off                              | Enables silent and lossy coerces.
 extra_float_digits                  | 0                                | Sets the number of digits displayed for floating-point values.
 idle_in_transaction_session_timeout | 0                                | Sets the maximum allowed duration of any idling transaction.
 idle_session_timeout                | 0                                | Sets the maximum allowed session duration after any statement.
 lc_collate                          | C                                | Shows the collation order locale.
 lc_ctype                            | C                                | Shows the character classification and case conversion locale.
 lc_time                             | C                                | Sets the locale for formatting date and time values.
 listen_addresses                    | *                                | Sets the host name or IP address(es) to listen to.
 log_timezone                        | UTC                              | Sets the time zone to use in log messages.
 max_connections                     | 2300                             | Sets the maximum number of concurrent connections.
 max_index_keys                      | 32                               | Shows the maximum number of index keys.
 max_user_connections                | 2000                             | Sets the maximum number of concurrent user connections.
 max_wal_size                        | 16GB                             | Sets the WAL size that triggers a checkpoint.
 search_path                         | "$user", public                  | Sets the schema search order for names that are not schema-qualified.
 server_encoding                     | LATIN9                           | Sets the server (database) character set encoding.
 ssl                                 | on                               | Enables SSL connections.
 ssl_ca_dir                          | /mnt/ybdata/certs/trust-database | Location of the SSL certificate authority directory.
 ssl_cert_file                       | /mnt/ybdata/certs/ybd.crt        | Location of the SSL server certificate file.
 ssl_ciphers                         | ECDHE:!3DES:!DES:!NULL:!RC4      | Sets the list of allowed SSL ciphers.
 ssl_key_file                        | /mnt/ybdata/certs/ybd.key        | Location of the SSL server private key file.
 ssl_prefer_server_ciphers           | on                               | Give priority to server ciphersuite order.
 statement_timeout                   | 0                                | Sets the maximum allowed duration in milliseconds of any statement.
 tcp_keepalives_count                | 10                               | Maximum number of TCP keepalive retransmits.
 tcp_keepalives_idle                 | 10                               | Time between issuing TCP keepalives.
 tcp_keepalives_interval             | 1                                | Time between TCP keepalive retransmits.
 TimeZone                            | UTC                              | Sets the time zone for displaying and interpreting time stamps.
 transaction_isolation               | read committed                   | Sets the current transaction's isolation level.
```

--------------------------------

### Get Maximum Nickname

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/max_aggregate.html

This example shows how to retrieve the lexicographically largest nickname from the team table using the MAX function.

```sql
premdb=# SELECT MAX(nickname) FROM team;
  max
--------
 Wolves
(1 row)
```

--------------------------------

### Example Output of sys.query_explain

Source: https://docs.yellowbrick.com/7.4.3/systemtables/sys.query_explain.html

This example demonstrates the typical output format of the sys.query_explain view when a query is active, showing plan details for each node.

```text
       Watch every 2s	Thu Feb  1 15:34:20 2018

 plan_id | node_id | index | type | workers | query_plan
---------+---------+-------+------+---------+------------
(0 rows)
...
                                                                                  Watch every 2s	Thu Feb  1 15:34:28 2018

                  plan_id                    | node_id | index |      type       | workers |                                                    query_plan
----------------------------------------------+---------+-------+-----------------+---------+------------------------------------------------------------------------------------------------------------------
 v2i-xVAwlrav2rBiQmxF50CLB0fWdHyuoBvhOj7WMnU= |       0 |     0 | WRITE ROW STORE | all     | WRITE ROW STORE                                                                                                 +
                                             |         |       |                 |         | (newmatchstats.seasonid, newmatchstats.matchday, newmatchstats.htid, newmatchstats.atid, newmatchstats.moment)  +
                                             |         |       |                 |         | distribute none
 v2i-xVAwlrav2rBiQmxF50CLB0fWdHyuoBvhOj7WMnU= |       1 |     1 | INSERT INTO     | all     | INSERT INTO newmatchstats                                                                                       +
                                             |         |       |                 |         | (newmatchstats.seasonid, newmatchstats.matchday, newmatchstats.htid, newmatchstats.atid, newmatchstats.moment)
 v2i-xVAwlrav2rBiQmxF50CLB0fWdHyuoBvhOj7WMnU= |       3 |     2 | DISTRIBUTE HASH | all     | DISTRIBUTE HASH (degraded only)                                                                                 +
                                             |         |       |                 |         | (newmatchstats.seasonid, newmatchstats.matchday, newmatchstats.htid, newmatchstats.atid, newmatchstats.moment)  +
                                             |         |       |                 |         | distribute (newmatchstats.seasonid)
 v2i-xVAwlrav2rBiQmxF50CLB0fWdHyuoBvhOj7WMnU= |       2 |     3 | APPEND SCAN     | all     | APPEND SCAN                                                                                                     +
                                             |         |       |                 |         | (newmatchstats.seasonid, newmatchstats.matchday, newmatchstats.htid, newmatchstats.atid, newmatchstats.moment)
 v2i-xVAwlrav2rBiQmxF50CLB0fWdHyuoBvhOj7WMnU= |       5 |     4 | TRANSPOSE       | all     | |-TRANSPOSE                                                                                                     +
                                             |         |       |                 |         |   (newmatchstats.seasonid, newmatchstats.matchday, newmatchstats.htid, newmatchstats.atid, newmatchstats.moment)
 v2i-xVAwlrav2rBiQmxF50CLB0fWdHyuoBvhOj7WMnU= |       4 |     5 | SCAN            | all     | | SCAN newmatchstats                                                                                            +
                                             |         |       |                 |         |   (newmatchstats.seasonid, newmatchstats.matchday, newmatchstats.htid, newmatchstats.atid, newmatchstats.moment)+
                                             |         |       |                 |         |   distribute (newmatchstats.seasonid)
 v2i-xVAwlrav2rBiQmxF50CLB0fWdHyuoBvhOj7WMnU= |       7 |     6 | SCAN ROW STORE  | single  | |-SCAN ROW STORE                                                                                                +
                                             |         |       |                 |         |   (newmatchstats.seasonid, newmatchstats.matchday, newmatchstats.htid, newmatchstats.atid, newmatchstats.moment)+
                                             |         |       |                 |         |   distribute none
(7 rows)
```

--------------------------------

### Enable SQL keystore configuration

Source: https://docs.yellowbrick.com/7.4.3/platforms/appliance/ybcli/cli_config_keystore.html

Use this command to initiate the configuration of the keystore for SQL integration.

```bash
config keystore sql enable
```

--------------------------------

### Configure Network Timeout

Source: https://docs.yellowbrick.com/7.4.3/platforms/cloud/ee/cloud_install/cloud_install_timeouts.html

Example of overriding the network timeout setting within the installer configuration file using a JSON path.

```json
{
    "network": {
        "timeout": "15m"
    }
}
```

--------------------------------

### Modify WLM Resource Pool Examples

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/alter_wlm_pool.html

Examples demonstrating how to update profiles, resource settings, and rename pools.

```sql
premdb=# alter wlm resource pool bobr_pool (profile bobr_profile);
ALTER WLM RESOURCE POOL
```

```sql
premdb=# alter wlm resource pool bobr_pool (slots 5, queue_size 10);
ALTER WLM RESOURCE POOL
```

```sql
premdb=# alter wlm resource pool longpool rename to longest_pool;
ALTER WLM RESOURCE POOL
```

--------------------------------

### Get First Thursday of Year 2000

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/next_day.html

This example shows how to find the date of the first Thursday in the year 2000 using a date literal.

```SQL
premdb=# select next_day(date '1999-12-31','Th') from sys.const;
  next_day
------------
 2000-01-06
(1 row)
```

--------------------------------

### Get Current Transaction Timestamp

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/localtimestamp.html

Use LOCALTIMESTAMP to retrieve the timestamp at the start of the current transaction. The value remains constant throughout the transaction.

```sql
SELECT LOCALTIMESTAMP;
```

--------------------------------

### Start Replica

Source: https://docs.yellowbrick.com/7.4.3/replication/replication_benchmarks.html

Use this SQL command to start a previously added replica. This command initiates the replication process.

```sql
ALTER DATABASE "<database_name>" ALTER REPLICA "<replica_name>" START;
```

--------------------------------

### Get the next value from a sequence

Source: https://docs.yellowbrick.com/7.4.3/systemtables/sys.sequence.html

This example shows how to retrieve the next available value from a previously created sequence using the nextval function.

```sql
premdb=> select nextval('matchid');
 nextval
---------
   51199
(1 row)
```

--------------------------------

### Hash-Based Partitioning Example

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/partitioning_options.html

Creating a table with 100 hash partitions based on a date column.

```sql
premdb=# create table newmatchstats(seasonid smallint not null, matchday date, htid smallint, atid smallint, moment varchar(5))
distribute on (seasonid)
partition by (hash(matchday with 100 partitions, is null));
CREATE TABLE
premdb=# \d newmatchstats
       Table "public.newmatchstats"
  Column  |         Type         | Modifiers
----------+----------------------+-----------
 seasonid | smallint             | not null
 matchday | date                 |
 htid     | smallint             |
 atid     | smallint             |
 moment   | character varying(5) |

Distribution: Hash (seasonid)
Partition Columns:
   "matchday"	 HASH (WITH 100 PARTITIONS)
```

--------------------------------

### Using DIV Function in SQL

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/div.html

Example demonstrating the creation of a table, insertion of a DECIMAL value, and the use of the DIV function to get the integer quotient.

```sql
premdb=# create table divtest(c1 decimal(5,3));
CREATE TABLE
premdb=# insert into divtest values(99.9);
INSERT 0 1
premdb=# select c1, div(c1,33.3) from divtest;
   c1   | div
--------+-----
 99.900 |   3
(1 row)
```

--------------------------------

### Get the first point of a LINESTRING using ST_StartPoint

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/st_startpoint.html

Use ST_StartPoint to extract the starting point from a LINESTRING. Ensure the input is cast to GEOGRAPHY for geospatial functions.

```sql
SELECT ST_AsText(ST_StartPoint('LINESTRING(1 1, 2 2, 3 3)'::GEOGRAPHY));
-- POINT(1 1)
```

--------------------------------

### Example file prefix output

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/external_format_unload_options.html

Demonstrates the consecutive numbering format when a custom file prefix is applied.

```text
04-30-18_1_1.csv
04-30-18_1_2.csv
04-30-18_1_3.csv
04-30-18_1_4.csv
04-30-18_1_5.csv
...
```

--------------------------------

### Create WLM Profile Example

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/create_wlm_profile.html

This example demonstrates creating a WLM profile named 'shortquerybias'. Only superusers or users with CONTROL WLM ON SYSTEM privilege can execute this command.

```sql
premdb=# create wlm profile shortquerybias;
CREATE WLM PROFILE
```

--------------------------------

### Get Closing Matchday per Season

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/max_aggregate.html

This example demonstrates using the MAX function to find the latest matchday for each season. Ensure the matchday column is not null.

```sql
premdb=# SELECT seasonid, MAX(matchday)
FROM match WHERE matchday IS NOT NULL
GROUP BY seasonid ORDER BY seasonid;
 seasonid |         max
----------+---------------------
       2 | 1994-05-08 00:00:00
       3 | 1995-05-14 00:00:00
       4 | 1996-05-04 00:00:00
       5 | 1997-05-11 00:00:00
       6 | 1998-05-10 00:00:00
       7 | 1999-05-16 00:00:00
       8 | 2000-05-14 00:00:00
       9 | 2001-05-19 00:00:00
      10 | 2002-05-11 00:00:00
...
```

--------------------------------

### Get Transaction Start Time with LOCALTIME

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/localtime.html

Use LOCALTIME to retrieve the time of day when the current transaction began. This value remains constant throughout the transaction.

```sql
select localtime from sys.const;
```

```sql
select localtime(6) from sys.const;
```

--------------------------------

### Create a sequence and query sys.sequence

Source: https://docs.yellowbrick.com/7.4.3/systemtables/sys.sequence.html

Demonstrates creating a sequence with a specific start value and then querying the sys.sequence view to inspect its properties.

```sql
premdb=# create sequence matchid start 50000;
CREATE SEQUENCE
```

```sql
premdb=> select * from sys.sequence;
 database_id | schema_id | sequence_id | owner_id |  name   |    fullname    | start_value | last_value |      min_value       |      max_value      | is_temp |         creation_time
-------------+-----------+-------------+----------+---------+----------------+-------------+------------+----------------------+---------------------+---------+-------------------------------
      16400 |     24638 |       25507 |    25048 | matchid | premdb.matchid |          49 |         49 | -9223372036854775807 | 9223372036854775807 | f       | 2022-09-13 14:05:13.911498-07
(1 row)
```

--------------------------------

### Get Bundled Assets

Source: https://docs.yellowbrick.com/7.4.3/platforms/cloud/ee/cloud_install/cloud_deployer_cli_ref.html

Outputs a JSON of assets bundled by the Yellowbrick deployer. This is useful during a self-managed install to find the version of Helm charts or Docker images.

```APIDOC
## GET /yb-install get-assets

### Description
Outputs a JSON of assets bundled by the Yellowbrick deployer. This can be useful during a self-managed install to find the version of Helm charts or Docker images.

### Method
GET

### Endpoint
/yb-install get-assets

### Parameters
No parameters are required for this command.

### Request Example
```json
{
  "example": "./yb-install get-assets"
}
```

### Response
#### Success Response (200)
- **containerImages** (array) - List of container images with their repository and tag.
- **helmCharts** (array) - List of Helm charts with their repository and version.

#### Response Example
```json
{
  "example": "{\"containerImages\":[{\"repo\":\"yellowbrick/yellowbrick-operator\",\"tag\":\"v1.2.3\"}], \"helmCharts\":[{\"repo\":\"yellowbrick/yellowbrick-operator\",\"version\":\"0.1.0\"}] }"
}
```
```

--------------------------------

### Create and Connect as a New User

Source: https://docs.yellowbrick.com/7.4.3/systemtables/sys.database.html

Demonstrates creating a new user and connecting to the database as that user to observe default visibility settings. New users initially have `CONNECT` and `TEMP` privileges on all databases via the `PUBLIC` role.

```sql
premdb=# create user noprivs;
CREATE ROLE
premdb=# alter user noprivs password 'noprivs';
ALTER ROLE
premdb=# \c premdb noprivs
Password for user noprivs:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "premdb" as user "noprivs".
premdb=> select database_id, name from sys.database                                                                                                             ;
 database_id |         name
-------------+-----------------------
       4400 | yellowbrick
      16392 | yellowbrick_test
      16393 | yellowbrick_test_utf8
      19123 | premdb
(4 rows)
```

--------------------------------

### Transaction Control Example in PL/pgSQL

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/create_procedure.html

Illustrates how to implement transaction control (COMMIT and ROLLBACK) within a PL/pgSQL stored procedure. A new transaction starts automatically after a COMMIT or ROLLBACK.

```sql
CREATE PROCEDURE transaction_test1()
AS $$
BEGIN
   FOR i IN 0..9 LOOP
       INSERT INTO test1 (a) VALUES (i);
       IF i % 2 = 0 THEN
           COMMIT;
       ELSE
           ROLLBACK;
       END IF;
   END LOOP;
END
$$ LANGUAGE plpgsql;
```

```sql
create table test1(a int);
```

```sql
CALL transaction_test1();
```

--------------------------------

### Create External Mount with Read/Write Verification Example

Source: https://docs.yellowbrick.com/7.4.3/ybd_sqlref/create_external_mount.html

Example of creating an external mount named '/qumulo/ext_db/' with specified GID and UID, verifying both read and write access.

```sql
premdb=# create external mount '/qumulo/ext_db/' as
'nfs://qumulo:/data/bobrum/ext_db/'
with (gid 1234, uid 4567);
```
Tokens:
Raw