Table of Contents

• Installation / Requirements
• Overview
• Contributing
• License

YbEasyCli

An extensible collection of utilities used to interact with a Yellowbrick Data Warehouse instance through an command line interface.

Installation / Requirements

Prerequisites

1. ybtools: Yellowbrick client tools package
2. Python
   • Users are encouraged to use Python 3.latest, but all utilities are currently compatible with Python 2.7 as well. -- to use Python 2.7 explicitly place the python command at the beginning of the command-line.
   • Executing any utility in this project as a standalone executable will default to Python 3.

Setting Environment Variables (Optional)

All utilities rely on establishing a connection with a Yellowbrick instance. Connection parameters are typically specified using command line flags. Optionally, a user may save these parameters in environment variables.

See the Yellowbrick documentation for more information about setting environment for ybsql connections.

Overview

Runnable Utilities

• yb_analyze_columns: Analyze the data content of a table's columns.
• yb_check_db_views: Check for broken views.
• yb_chunk_dml_by_date_part: Chunk DML by DATE/TIMESTAMP column.
• yb_chunk_dml_by_integer_yyyymmdd: Chunk DML by YYYYMMDD integer column.
• yb_chunk_dml_by_integer: Chunk DML by INTEGER column.
• yb_chunk_optimal_rows: Determine the optimal number of rows per chunk for a table(experimental).
• yb_create_calendar_table: Create a calendar dimension table.
• yb_create_dev_db: Create a new development DB based on an existing DB.
• yb_create_log_query_history: Build/update long term history db table/views sourced from the sys.log_query view.
• yb_create_loopback_remote_server: Create a loopback remote server for testing database replication.
• yb_ddl_sequence: Return the sequence/s DDL for the requested database. Use sequence filters to limit the set of sequences returned.
• yb_ddl_stored_proc: Return the stored procedure/s DDL for the requested database. Use stored procedure filters to limit the set of stored procedures returned.
• yb_ddl_table: Return the table/s DDL for the requested database. Use table filters to limit the set of tables returned.
• yb_ddl_view: Return the view/s DDL for the requested database. Use view filters to limit the set of views returned.
• yb_exec_ybtool: Execute a ybtool with a unified DB connection arguments/method.
• yb_find_columns: List column names and column attributes for filtered columns.
• yb_get_column_name: List/Verifies that the specified table/view column name if it exists.
• yb_get_column_names: List/Verifies that the specified column names exist.
• yb_get_column_type: Return the data type of the requested column.
• yb_get_query: Get the SQL of a query by it's query_id, including base64/gzipped SQL.
• yb_get_sequence_names: List/Verifies that the specified sequence/s exist.
• yb_get_stored_proc_names: List/Verifies that the specified stored procedures/s exist.
• yb_get_table_distribution_key: Identify the distribution column or type (random or replicated) of the requested table.
• yb_get_table_name: List/Verifies that the specified table exists.
• yb_get_table_names: List/Verifies that the specified table/s exist.
• yb_get_view_name: List/Verifies that the specified view exists.
• yb_get_view_names: List/Verifies that the specified view/s exist.
• yb_is_cstore_table: Determine if a table is stored as a column store table.
• yb_mass_column_update: Update the value of multiple columns.
• yb_query_to_stored_proc: Create a stored procedure for the provided query with the query privileges of the definer/creator.
• yb_sys_query_to_user_table: Convert system query to user table.
• yb_sysprocs_all_user_objs: Report all user objects in all databases with owner and ACL details.
• yb_sysprocs_column_dstr: Distribution of rows per distinct values for column grouped on a logarithmic scale.
• yb_sysprocs_column_stats: Table column metdata including estimates from statistics.
• yb_sysprocs_load: Transformed subset of sys.load columns for active bulk loads.
• yb_sysprocs_log_bulk_xfer: Transformed subset active bulk transfers (ybload & ybunload) from sys.load and sys.unload.
• yb_sysprocs_log_query: Details on completed backend statements.
• yb_sysprocs_log_query_pivot: Queries for the last week aggregated by hour for use in WLM pivot table analysis.
• yb_sysprocs_log_query_slot_usage: Create a WLM slot usage report by analyzing sys.log_query data.
• yb_sysprocs_log_query_smry: Aggregated subset of the sys.log_query data.
• yb_sysprocs_log_query_steps: Completed statements actual vs plan metrics by plan node.
• yb_sysprocs_log_query_timing: Details on completed backend statements.
• yb_sysprocs_procedure: User created stored procedures.
• yb_sysprocs_query: Transformed subset of sys.query columns for currently running statements.
• yb_sysprocs_query_rule_events: Return the WLM rule events for a query.
• yb_sysprocs_query_steps: Currently executing statements actual vs plan metrics by plan node.
• yb_sysprocs_rel: All user "relations" (tables, views, & sequences) in all databases.
• yb_sysprocs_rowstore: Rowstore overal metrics including size of data in user tables.
• yb_sysprocs_rowstore_by_table: Size of rowstore data in user tables across all databases.
• yb_sysprocs_schema: All user schemas across all databases.
• yb_sysprocs_session: Current session state details.
• yb_sysprocs_session_smry: Current sessions aggregated by db, user, state, app, ip, etc...
• yb_sysprocs_storage: Aggregated summary of appliance storage report.
• yb_sysprocs_storage_by_db: Table storage report.
• yb_sysprocs_storage_by_schema: Storage summary by schema across one or more databases.
• yb_sysprocs_storage_by_table: Table storage report.
• yb_sysprocs_sysviews: Names and arguments for all installed sysviews procedures.
• yb_sysprocs_table_constraints: Existing constraints on user tables as per information_schema.table_constraints.
• yb_sysprocs_table_skew: Table skew report.
• yb_sysprocs_wlm_active_profile: Returns current active WLM profile configuration details by pool.
• yb_sysprocs_wlm_active_rule: Current active WLM profile rules.
• yb_sysprocs_wlm_profile_rule: Current active or named WLM detailed profile rules.
• yb_sysprocs_wlm_state: Returns current active WLM profile state metrics by pool.
• yb_to_yb_copy_table: Copy a table from a source cluster to a destination cluster.
• yb_wl_profiler_heatmap: Creates a 35 day Excel heatmap of Work Loads on a Yellowbrick Cluster.

Other Files

• yb_util: Parent class for all utilities

• yb_common: Performs functions such as argument parsing, login verification, logging, and command execution that are common to all utilities in this project.

• yb_ddl_object: Dump out the SQL/DDL that was used to create any database object.

  • This file is typically not executed directly, but it is relied upon by:
    1. yb_ddl_sequence
    2. yb_ddl_table
    3. yb_ddl_view

• test_create_host_objects: Create test user, database, and database objects.

• test_drop_host_objects: Drop test user, database, and database objects.

• test_run: Runs the test created for all utilities or a given utility.

Contributing

Contributors will be expected to sign the Contributors License Agreement associated with this project. A bot will evaluate whether the CLA has been signed when you create a pull request. If necessary, the bot will leave a comment prompting you to accept the agreement.

Before creating a pull request, ensure that changes are properly tested. See the guide for running and developing tests for more information.

License

YbEasyCli is distributed under the MIT License. Using and modifying these utilities should be done at your own risk.