Skip to content

Cherry-pick #15845 to 7.x: SQL module now supports key/value mode#15870

Merged
jsoriano merged 2 commits intoelastic:7.xfrom
jsoriano:backport_15845_7.x
Jan 27, 2020
Merged

Cherry-pick #15845 to 7.x: SQL module now supports key/value mode#15870
jsoriano merged 2 commits intoelastic:7.xfrom
jsoriano:backport_15845_7.x

Conversation

@jsoriano
Copy link
Copy Markdown
Member

@jsoriano jsoriano commented Jan 27, 2020

Cherry-pick of PR #15845 to 7.x branch. Original message:

What does this PR do?

This PR allows to SQL generic module to send events correctly when the query return results in the way of key/values format.

Why is it important?

Because there are some querys like mysql SHOW STATUS that display the results in a key/value format so they need to be scanned in a different way, being the key the first column of the response and the value the second one of the response.

Checklist

  • My code follows the style guidelines of this project
  • I have commented my code, particularly in hard-to-understand areas
  • I have made corresponding changes to the documentation
  • I have made corresponding change to the default configuration files
  • I have added tests that prove my fix is effective or that my feature works

How to test this PR locally

Having a mysql database, enable and configure que sql module with console as output, variable mode and query SHOW STATUS.

For example

- module: sql
  metricsets:
    - query
  period: 10s
  hosts: ["root:secret@tcp(127.0.0.1:3306)/"]

  driver: "mysql"
  sql_query: "SHOW STATUS"
  sql_response_format: variable

Running metricbeat -e you will see only one event with all key/value pairs:

...
 "metrics": {
      "numeric": {
        "com_alter_tablespace": 0,
        "com_drop_event": 0,
        "com_admin_commands": 1,
        "slow_queries": 0,
        "com_show_tables": 0,
        "innodb_buffer_pool_read_ahead_rnd": 0,
        "com_delete": 0,
        "innodb_data_reads": 784,
        "handler_read_first": 0,
        "com_reset": 0,
        "innodb_row_lock_current_waits": 0,
        "com_show_keys": 0,
        "performance_schema_digest_lost": 0,
        "qcache_hits": 0,
        "select_scan": 0,
        "performance_schema_locker_lost": 0,
        "performance_schema_stage_classes_lost": 0,
        "com_xa_commit": 0,
        "com_purge_before_date": 0,
        "performance_schema_accounts_lost": 0,
        "com_rollback_to_savepoint": 0,
        "binlog_cache_disk_use": 0,
        "key_blocks_not_flushed": 0,
        "com_change_master": 0,
        "innodb_buffer_pool_bytes_data": 7.487488e+06,
        "sort_range": 0,
        "ssl_sessions_reused": 0,
        "threads_running": 1,
        "innodb_buffer_pool_pages_dirty": 0,
        "queries": 21,
        "com_show_storage_engines": 0,
        "handler_discover": 0,
        "ssl_callback_cache_hits": 0,
        "innodb_data_pending_reads": 0,
        "com_begin": 0,
        "com_show_engine_mutex": 0,
        "max_used_connections": 1,
        "com_show_profiles": 0,
        "com_show_procedure_status": 0,
        "com_drop_view": 0,
        "handler_savepoint": 0,
        "performance_schema_session_connect_attrs_lost": 0,
        "qcache_lowmem_prunes": 0,
        "handler_rollback": 0,
        "com_show_master_status": 0,
        "ssl_verify_mode": 0,
        "flush_commands": 1,
        "connections": 8,
        "com_show_collations": 0,
        "handler_commit": 0,
        "com_show_create_proc": 0,
        "com_stmt_reprepare": 0,
        "com_purge": 0,
        "com_do": 0,
        "com_show_procedure_code": 0,
        "handler_read_last": 0,
        "com_savepoint": 0,
        "innodb_available_undo_logs": 128,
        "com_show_engine_logs": 0,
        "handler_read_rnd": 0,
        "com_rename_user": 0,
        "handler_write": 0,
        "performance_schema_mutex_instances_lost": 0,
        "com_stmt_fetch": 0,
        "com_create_user": 0,
        "innodb_buffer_pool_pages_total": 8191,
        "binlog_cache_use": 0,
        "com_show_profile": 0,
        "com_checksum": 0,
        "not_flushed_delayed_rows": 0,
        "performance_schema_statement_classes_lost": 0,
        "connection_errors_peer_address": 0,
        "com_help": 0,
        "last_query_cost": 0,
        "com_rename_table": 0,
        "com_show_slave_status": 0,
        "questions": 1,
        "binlog_stmt_cache_disk_use": 0,
        "com_insert": 0,
        "com_stmt_send_long_data": 0,
        "com_show_status": 1,
        "connection_errors_tcpwrap": 0,
        "ssl_default_timeout": 0,
        "ssl_session_cache_overflows": 0,
        "performance_schema_file_instances_lost": 0,
        "performance_schema_file_handles_lost": 0,
        "com_release_savepoint": 0,
        "com_select": 0,
        "com_xa_rollback": 0,
        "innodb_log_write_requests": 1,
        "innodb_buffer_pool_write_requests": 1,
        "performance_schema_table_handles_lost": 0,
        "com_repair": 0,
        "com_xa_end": 0,
        "com_show_events": 0,
        "innodb_buffer_pool_read_ahead": 0,
        "handler_update": 0,
        "ssl_session_cache_misses": 0,
        "innodb_data_pending_fsyncs": 0,
        "performance_schema_table_instances_lost": 0,
        "com_show_create_trigger": 0,
        "com_show_table_status": 0,
        "opened_files": 115,
        "innodb_buffer_pool_reads": 458,
        "innodb_pages_written": 1,
        "qcache_inserts": 0,
        "innodb_os_log_pending_writes": 0,
        "innodb_buffer_pool_read_requests": 19969,
        "innodb_buffer_pool_pages_flushed": 1,
        "innodb_buffer_pool_pages_data": 457,
        "innodb_rows_deleted": 0,
        "innodb_data_writes": 5,
        "key_blocks_used": 0,
        "connection_errors_select": 0,
        "tc_log_max_pages_used": 0,
        "com_show_relaylog_events": 0,
        "com_create_procedure": 0,
        "ssl_ctx_verify_mode": 0,
        "com_show_open_tables": 0,
        "performance_schema_file_classes_lost": 0,
        "com_grant": 0,
        "table_open_cache_hits": 0,
        "com_show_engine_status": 0,
        "com_show_plugins": 0,
        "com_alter_db": 0,
        "prepared_stmt_count": 0,
        "ssl_session_cache_hits": 0,
        "com_stmt_close": 0,
        "bytes_received": 105,
        "innodb_log_writes": 1,
        "qcache_free_memory": 1.031352e+06,
        "performance_schema_socket_instances_lost": 0,
        "sort_scan": 0,
        "com_create_table": 0,
        "handler_read_next": 0,
        "com_slave_start": 0,
        "qcache_not_cached": 0,
        "com_drop_server": 0,
        "com_set_option": 0,
        "aborted_clients": 0,
        "open_files": 16,
        "table_open_cache_overflows": 0,
        "ssl_session_cache_timeouts": 0,
        "handler_read_rnd_next": 0,
        "innodb_rows_read": 0,
        "created_tmp_disk_tables": 0,
        "ssl_accepts": 0,
        "com_alter_event": 0,
        "binlog_stmt_cache_use": 0,
        "com_analyze": 0,
        "key_read_requests": 0,
        "com_truncate": 0,
        "table_locks_waited": 0,
        "bytes_sent": 100,
        "ssl_verify_depth": 0,
        "com_dealloc_sql": 0,
        "ssl_finished_connects": 0,
        "com_stmt_reset": 0,
        "key_reads": 0,
        "com_alter_user": 0,
        "connection_errors_max_connections": 0,
        "com_kill": 0,
        "table_open_cache_misses": 0,
        "performance_schema_mutex_classes_lost": 0,
        "select_full_join": 0,
        "key_writes": 0,
        "com_drop_procedure": 0,
        "com_revoke": 0,
        "com_show_function_status": 0,
        "ssl_accept_renegotiates": 0,
        "opened_table_definitions": 0,
        "opened_tables": 0,
        "com_show_databases": 0,
        "innodb_data_pending_writes": 0,
        "performance_schema_rwlock_classes_lost": 0,
        "com_update_multi": 0,
        "innodb_row_lock_time_avg": 0,
        "innodb_buffer_pool_bytes_dirty": 0,
        "innodb_row_lock_time": 0,
        "innodb_page_size": 16384,
        "handler_external_lock": 0,
        "innodb_data_fsyncs": 5,
        "com_lock_tables": 0,
        "com_xa_prepare": 0,
        "uptime_since_flush_status": 135,
        "com_show_create_table": 0,
        "threads_cached": 0,
        "com_call_procedure": 0,
        "innodb_truncated_status_writes": 0,
        "delayed_errors": 0,
        "com_show_privileges": 0,
        "qcache_queries_in_cache": 0,
        "com_change_db": 0,
        "com_show_grants": 0,
        "com_stmt_execute": 0,
        "aborted_connects": 0,
        "com_show_warnings": 0,
        "com_drop_user": 0,
        "com_optimize": 0,
        "performance_schema_cond_instances_lost": 0,
        "com_replace": 0,
        "com_show_function_code": 0,
        "performance_schema_users_lost": 0,
        "com_alter_table": 0,
        "com_empty_query": 0,
        "innodb_log_waits": 0,
        "com_rollback": 0,
        "innodb_row_lock_waits": 0,
        "com_prepare_sql": 0,
        "innodb_buffer_pool_pages_free": 7731,
        "com_show_create_event": 0,
        "com_alter_procedure": 0,
        "com_create_index": 0,
        "delayed_insert_threads": 0,
        "com_insert_select": 0,
        "threads_connected": 1,
        "com_show_create_db": 0,
        "slave_open_temp_tables": 0,
        "com_resignal": 0,
        "sort_merge_passes": 0,
        "com_check": 0,
        "handler_prepare": 0,
        "com_flush": 0,
        "qcache_free_blocks": 1,
        "innodb_buffer_pool_pages_misc": 3,
        "created_tmp_tables": 0,
        "ssl_session_cache_size": 0,
        "com_get_diagnostics": 0,
        "com_show_charsets": 0,
        "com_update": 0,
        "com_create_udf": 0,
        "com_show_triggers": 0,
        "com_uninstall_plugin": 0,
        "table_locks_immediate": 70,
        "com_load": 0,
        "com_show_binlogs": 0,
        "last_query_partial_plans": 0,
        "innodb_rows_inserted": 0,
        "com_create_db": 0,
        "com_unlock_tables": 0,
        "ssl_used_session_cache_entries": 0,
        "com_show_variables": 0,
        "performance_schema_socket_classes_lost": 0,
        "com_xa_start": 0,
        "com_execute_sql": 0,
        "innodb_dblwr_writes": 1,
        "performance_schema_thread_instances_lost": 0,
        "com_drop_table": 0,
        "innodb_os_log_written": 1024,
        "created_tmp_files": 6,
        "com_drop_trigger": 0,
        "innodb_num_open_files": 27,
        "performance_schema_rwlock_instances_lost": 0,
        "com_revoke_all": 0,
        "com_create_view": 0,
        "com_create_server": 0,
        "handler_mrr_init": 0,
        "com_install_plugin": 0,
        "connection_errors_accept": 0,
        "innodb_rows_updated": 0,
        "com_drop_db": 0,
        "select_full_range_join": 0,
        "com_slave_stop": 0,
        "com_alter_db_upgrade": 0,
        "connection_errors_internal": 0,
        "open_tables": 60,
        "performance_schema_hosts_lost": 0,
        "com_create_function": 0,
        "handler_read_key": 0,
        "com_show_fields": 0,
        "handler_savepoint_rollback": 0,
        "sort_rows": 0,
        "com_replace_select": 0,
        "innodb_dblwr_pages_written": 1,
        "performance_schema_cond_classes_lost": 0,
        "com_create_trigger": 0,
        "com_show_create_func": 0,
        "slow_launch_threads": 0,
        "com_binlog": 0,
        "innodb_os_log_pending_fsyncs": 0,
        "uptime": 135,
        "tc_log_page_size": 0,
        "ssl_finished_accepts": 0,
        "com_alter_server": 0,
        "open_table_definitions": 67,
        "ssl_client_connects": 0,
        "com_drop_function": 0,
        "com_ha_open": 0,
        "innodb_buffer_pool_read_ahead_evicted": 0,
        "com_delete_multi": 0,
        "key_write_requests": 0,
        "com_ha_close": 0,
        "select_range": 0,
        "delayed_writes": 0,
        "com_ha_read": 0,
        "com_xa_recover": 0,
        "ssl_ctx_verify_depth": 0,
        "innodb_pages_created": 0,
        "com_commit": 0,
        "com_assign_to_keycache": 0,
        "com_show_binlog_events": 0,
        "open_streams": 0,
        "ssl_connect_renegotiates": 0,
        "qcache_total_blocks": 1,
        "innodb_os_log_fsyncs": 3,
        "performance_schema_thread_classes_lost": 0,
        "handler_read_prev": 0,
        "tc_log_page_waits": 0,
        "com_show_slave_hosts": 0,
        "com_create_event": 0,
        "com_signal": 0,
        "com_show_errors": 0,
        "com_stmt_prepare": 0,
        "com_alter_function": 0,
        "com_drop_index": 0,
        "handler_delete": 0,
        "com_preload_keys": 0,
        "innodb_row_lock_time_max": 0,
        "threads_created": 1,
        "innodb_pages_read": 457,
        "innodb_data_written": 34816,
        "innodb_data_read": 9.670656e+06,
        "select_range_check": 0,
        "innodb_buffer_pool_wait_free": 0,
        "key_blocks_unused": 6698,
        "com_show_processlist": 0
      },
      "string": {
        "ssl_version": "",
        "slave_received_heartbeats": "",
        "innodb_have_atomic_builtins": "ON",
        "ssl_server_not_after": "",
        "slave_heartbeat_period": "",
        "ssl_server_not_before": "",
        "innodb_buffer_pool_dump_status": "not started",
        "innodb_buffer_pool_load_status": "not started",
        "slave_retried_transactions": "",
        "slave_last_heartbeat": "",
        "rsa_public_key": "",
        "ssl_session_cache_mode": "NONE",
        "ssl_cipher": "",
        "compression": "OFF",
        "ssl_cipher_list": "",
        "slave_running": "OFF"
      }
...

This PR introduces a new configuration option called sql_response_format with 2 available values:

  • table: This is the current behaviour
  • variable: This is to indicate when the response format is like a key/value table

Any other value will exit with an error. The configuration option and their values were suggested in the issue #15770

Related issues

@rvillablanca @jsoriano
Add support for SQL responses in key/value format (elastic#15845)
a41a4d6 
There are some queries like mysql `SHOW STATUS` that display the
results in a key/value format so they need to be scanned in a different
way, being the key the first column of the response and the value the
second one.

(cherry picked from commit d8517c6)
@jsoriano jsoriano requested a review from a team as a code owner January 27, 2020 16:45
@jsoriano jsoriano merged commit e4f228c into elastic:7.x Jan 27, 2020
@jsoriano jsoriano deleted the backport_15845_7.x branch January 27, 2020 23:10
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

1 more reviewer

@kaiyan-sheng kaiyan-sheng kaiyan-sheng approved these changes

Reviewers whose approvals may not affect merge requirements

Assignees

No one assigned

Labels

backport review

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@jsoriano @kaiyan-sheng @rvillablanca