### 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= --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 "" ALTER REPLICA "" 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); ```