SQL Scripts

git.io/SQL

Useful SQL scripts, split from DevOps Bash tools, for which this is now a submodule.

Hari Sekhon

Cloud & Big Data Contractor, United Kingdom

(you're welcome to connect with me on LinkedIn)

Inventory

DevOps / DBA

aws_athena_cloudtrail_ddl.sql - AWS Athena DDL to setup up integration to query CloudTrail logs from Athena
bigquery_*.sql - Google BigQuery scripts:
- bigquery_billing_*.sql - billing queries for GCP usage eg. highest cost services, most used GCP products, recent charges etc.
- bigquery_info_*.sql - information schema queries for datasets, tables, columns, partitioning, clustering etc.
mysql_*.sql:
- MySQL / MariaDB queries for DBA investigating + performance tuning
- mysql_info.sql - summary overview, useful to debug your mysql.user table auth effects
  - (shows intended USER() vs actual CURRENT_USER())
- tested on MySQL 5.5, 5.6, 5.7, 8.0 and MariaDB 5.5, 10.x
postgres_*.sql:
- PostgreSQL queries for DBA investigating + performance tuning
- postgres_info.sql - big summary overview, recommend you start here
- tested on PostgreSQL 8.4, 9.x, 10.x, 11.x, 12.x, 13.x

Analytics

bigquery_*.sql - Google BigQuery scripts:
- bigquery_billing_*.sql - billing queries for GCP usage eg. highest cost services, most used GCP products, recent charges etc.
- bigquery_info_*.sql - information schema queries for datasets, tables, columns, partitioning, clustering etc.
- analytics/bigquery_*.sql - ecommerce queries and BigQuery ML machine learning classification logistic regression models and purchasing predictions
- for more BigQuery examples, see Data Engineering demos

DevOps SQL tooling

You can quickly test the PostgreSQL / MySQL scripts using postgres.sh / mysqld.sh / mariadb.sh in the DevOps Bash tools repo, which boots a docker container and drops straight in to a mysql / psql shell with this directory mounted at /sql and used as $PWD for fast easy sourcing eg.

postgres:

\i /sql/postgres_query_times.sql

\i postgres_query_times.sql

mysql:

source /sql/mysql_sessions.sql

\. mysql_sessions.sql

Related scripts

.psqlrc - advanced PostgreSQL psql client config
psql.sh - quickly connect to PostgreSQL with command line switches inferred from environment variables
mysql.sh - quickly connect to MySQL / MariaDB with command line switches inferred from environment variables
postgres.sh - one-touch PostgreSQL, boots docker container and drops you in to psql shell. Version can be given as an argument
mysqld.sh / mariadb.sh - one-touch MySQL / MariaDB, boots docker container and drops you in to mysql shell. Version can be given as an argument
postgres_foreach_table.sh / mysql_foreach_table.sh - execute templated SQL queries/statements against all or a subset of tables
postgres_tables_row_counts.sh / mysql_tables_row_counts.sh - get row counts for all or a subset of tables
sqlcase.pl - autocases your SQL code
- I use this a lot and call it via hotkey configured in my .vimrc
- there are *case.pl specializations for most of the major RDBMS and distributed SQL systems, even several NoSQL systems, using each one's language specific keywords
Hive & Impala SQL:
- beeline.sh - quickly connect to Hive, auto-determines HiveServer2 address, Kerberos & SSL options, ZooKeeper quorum
- impala_shell.sh - quickly connect to Impala, auto-determines a Hadoop worker node address and Kerberos options (can use an environment variable for a Load Balancer setup)
- hive_foreach_table.sh / impala_foreach_table.sh - execute templated SQL queries/statements against all or a subset of tables
- hive_tables_row_counts.sh / impala_tables_row_counts.sh - get row counts for all or a subset of tables
- hive_tables_column_counts.sh / impala_tables_column_counts.sh - get the column counts for big tables in Hive / Impala
- hive_tables_metadata.sh / impala_tables_metadata.sh / hive_tables_locations.sh / impala_tables_locations.sh - get Hive / Impala metadata for all or a subset of tables, eg. Location to determine where the external tables data is being stored (HDFS / S3 paths)

Name		Name	Last commit message	Last commit date
Latest commit History 795 Commits
.circleci		.circleci
.github		.github
analytics		analytics
setup		setup
.checkov.yaml		.checkov.yaml
.editorconfig		.editorconfig
.envrc		.envrc
.envrc-python		.envrc-python
.markdownlint.rb		.markdownlint.rb
.mdl.rb		.mdl.rb
.mdlrc		.mdlrc
.pre-commit-config.yaml		.pre-commit-config.yaml
.travis.yml		.travis.yml
LICENSE		LICENSE
Makefile		Makefile
README.md		README.md
aws_athena_cloudtrail_ddl.sql		aws_athena_cloudtrail_ddl.sql
bigquery_billing_commonly_charged_units_of_measure.sql		bigquery_billing_commonly_charged_units_of_measure.sql
bigquery_billing_latest_100_charges.sql		bigquery_billing_latest_100_charges.sql
bigquery_billing_products_most_frequently_used.sql		bigquery_billing_products_most_frequently_used.sql
bigquery_billing_products_with_highest_aggregate_cost.sql		bigquery_billing_products_with_highest_aggregate_cost.sql
bigquery_billing_products_with_most_billing_records.sql		bigquery_billing_products_with_most_billing_records.sql
bigquery_info_biggest_public_tables_by_row_count.sql		bigquery_info_biggest_public_tables_by_row_count.sql
bigquery_info_columns.sql		bigquery_info_columns.sql
bigquery_info_columns_partitioned_clustered.sql		bigquery_info_columns_partitioned_clustered.sql
bigquery_info_datasets.sql		bigquery_info_datasets.sql
bigquery_info_tables.sql		bigquery_info_tables.sql
mysql_databases_by_size.sql		mysql_databases_by_size.sql
mysql_host_summary.sql		mysql_host_summary.sql
mysql_indexes_unused.sql		mysql_indexes_unused.sql
mysql_info.sql		mysql_info.sql
mysql_memory_by_host.sql		mysql_memory_by_host.sql
mysql_memory_by_user.sql		mysql_memory_by_user.sql
mysql_sessions.sql		mysql_sessions.sql
mysql_statement_latency_by_host.sql		mysql_statement_latency_by_host.sql
mysql_statement_latency_by_user.sql		mysql_statement_latency_by_user.sql
mysql_tables.sql		mysql_tables.sql
mysql_tables_nonsystem.sql		mysql_tables_nonsystem.sql
mysql_user.sql		mysql_user.sql
mysql_user_summary.sql		mysql_user_summary.sql
mysql_users.sql		mysql_users.sql
mysql_users_pre56.sql		mysql_users_pre56.sql
mysql_users_pre8.sql		mysql_users_pre8.sql
mysql_views.sql		mysql_views.sql
mysql_views_system.sql		mysql_views_system.sql
postgres_active_query_count.sql		postgres_active_query_count.sql
postgres_backends_per_database.sql		postgres_backends_per_database.sql
postgres_blocked_queries.sql		postgres_blocked_queries.sql
postgres_columns_null.sql		postgres_columns_null.sql
postgres_columns_useless.sql		postgres_columns_useless.sql
postgres_databases_by_size.sql		postgres_databases_by_size.sql
postgres_databases_by_size_if_accessible.sql		postgres_databases_by_size_if_accessible.sql
postgres_dirs.sql		postgres_dirs.sql
postgres_dirs_pre10.sql		postgres_dirs_pre10.sql
postgres_dirs_pre11.9.sql		postgres_dirs_pre11.9.sql
postgres_grant_select_all_tables.sql		postgres_grant_select_all_tables.sql
postgres_idle_sessions.sql		postgres_idle_sessions.sql
postgres_idle_sessions_current_db_kill.sql		postgres_idle_sessions_current_db_kill.sql
postgres_idle_sessions_current_db_kill_pre92.sql		postgres_idle_sessions_current_db_kill_pre92.sql
postgres_idle_sessions_kill.sql		postgres_idle_sessions_kill.sql
postgres_idle_sessions_pre92.sql		postgres_idle_sessions_pre92.sql
postgres_index_cardinality.sql		postgres_index_cardinality.sql
postgres_index_cardinality_with_schema_name.sql		postgres_index_cardinality_with_schema_name.sql
postgres_indexes_cache_hit_ratio.sql		postgres_indexes_cache_hit_ratio.sql
postgres_indexes_unused.sql		postgres_indexes_unused.sql
postgres_info.sql		postgres_info.sql
postgres_info_pre10.sql		postgres_info_pre10.sql
postgres_last_analyze.sql		postgres_last_analyze.sql
postgres_last_analyze_pre94.sql		postgres_last_analyze_pre94.sql
postgres_last_vacuum.sql		postgres_last_vacuum.sql
postgres_last_vacuum_analyze.sql		postgres_last_vacuum_analyze.sql
postgres_last_vacuum_analyze_pre94.sql		postgres_last_vacuum_analyze_pre94.sql
postgres_last_vacuum_pre91.sql		postgres_last_vacuum_pre91.sql
postgres_locks.sql		postgres_locks.sql
postgres_locks_blocked.sql		postgres_locks_blocked.sql
postgres_locks_blocked_application.sql		postgres_locks_blocked_application.sql
postgres_locks_query_age.sql		postgres_locks_query_age.sql
postgres_locks_query_age_pre92.sql		postgres_locks_query_age_pre92.sql
postgres_queries_slow.sql		postgres_queries_slow.sql
postgres_queries_slow_pre92.sql		postgres_queries_slow_pre92.sql
postgres_queries_slow_pre96.sql		postgres_queries_slow_pre96.sql
postgres_query_cache_hit_ratio.sql		postgres_query_cache_hit_ratio.sql
postgres_query_times.sql		postgres_query_times.sql
postgres_query_times_pre13.sql		postgres_query_times_pre13.sql
postgres_query_times_pre95.sql		postgres_query_times_pre95.sql
postgres_recovery.sql		postgres_recovery.sql
postgres_running_queries.sql		postgres_running_queries.sql
postgres_running_queries_pre92.sql		postgres_running_queries_pre92.sql
postgres_sequences_restart_all.sql		postgres_sequences_restart_all.sql
postgres_sessions.sql		postgres_sessions.sql
postgres_sessions_pre10.sql		postgres_sessions_pre10.sql
postgres_sessions_state_count.sql		postgres_sessions_state_count.sql
postgres_settings.sql		postgres_settings.sql
postgres_settings_auth.sql		postgres_settings_auth.sql
postgres_settings_autovacuum.sql		postgres_settings_autovacuum.sql
postgres_settings_client_connection.sql		postgres_settings_client_connection.sql
postgres_settings_compatibility.sql		postgres_settings_compatibility.sql
postgres_settings_connections.sql		postgres_settings_connections.sql
postgres_settings_developer.sql		postgres_settings_developer.sql
postgres_settings_error_handling.sql		postgres_settings_error_handling.sql

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

SQL Scripts

Inventory

DevOps / DBA

Analytics

DevOps SQL tooling

Related scripts

Stargazers over time

More Core Repos

Knowledge

DevOps Code

Containerization

CI/CD

DBA - SQL

DevOps Reloaded

Templates

Misc

About

Languages

License

HariSekhon/SQL-scripts

Folders and files

Latest commit

History

Repository files navigation

SQL Scripts

Inventory

DevOps / DBA

Analytics

DevOps SQL tooling

Related scripts

Stargazers over time

More Core Repos

Knowledge

DevOps Code

Containerization

CI/CD

DBA - SQL

DevOps Reloaded

Templates

Misc

About

Topics

Resources

License

Stars

Watchers

Forks

Languages