Class: DWH::Adapters::SqlServer

Inherits:
Adapter
  • Object
show all
Defined in:
lib/dwh/adapters/sql_server.rb

Overview

Microsoft SQL Server adapter. This adapter requires the tiny_tds gem. For Mac OS and Linux, you will need to instal FreeTDS and most likely needs OpenSSL. Please follow the the instructions there before using the adapter.

Create and adatper instance using DWH.create.

Examples:

Basic connection with required only options

DWH.create(:sqlserver, {host: 'localhost', database: 'my_db', username: 'sa'})

Connect to Azuer SQL Server

DWH.create(:sqlserver, {host: 'localhost', database: 'my_db', username: 'sa', azure: true})

Connection sending custom application name

DWH.create(:sqlserver, {host: 'localhost', database: 'my_db', username: 'sa', client_name: 'Strata CLI'})

Pass extra connection params

DWH.create(:sqlserver, {host: 'localhost', database: 'my_db',
username: 'sa', client_name: 'Strata CLI',
extra_connection_params: {
  container: true,
  use_utf16: false
})

fetch tables in database

adapter.tables

fetch tables from another database

adapter.tables(catalog: 'other_db')

get table metadata for table in another db

adapter.metadata('other_db.dbo.my_table') or adapter.metadata('my_table', catalog: 'other_db')

Constant Summary

Constants included from Settings

Settings::BASE_SETTINGS_FILE

Constants included from Functions::Dates

Functions::Dates::DATE_CLASSES, Functions::Dates::TIMESTAMPABLE_UNITS

Instance Attribute Summary

Attributes inherited from Adapter

#config, #settings

Attributes included from Settings

#adapter_settings

Instance Method Summary collapse

Methods inherited from Adapter

#adapter_name, #alter_settings, #close, config, configuration, #connect!, #connect?, #extra_connection_params, #extra_query_params, #initialize, #reset_settings, #token_expired?, #with_debug, #with_retry

Methods included from Settings

#adapter_name, #load_settings, #settings_file, #settings_file_path, #using_base_settings?

Methods included from Logger

#logger, logger

Methods included from Behaviors

#apply_advanced_filtering_on_array_projections?, #cross_universe_measure_filtering_strategy, #extend_ending_date_to_last_hour_of_day?, #final_measure_filter?, #final_pass_measure_join_type, #greedy_apply_date_filters, #intermediate_measure_filter?, #temp_table_prefix, #temp_table_type

Methods included from Functions

#cast, #cross_join, #gsk, #lower_case, #quote, #string_lit, #trim, #upper_case

Methods included from Functions::Arrays

#array_exclude_list, #array_in_list, #array_unnest_join

Methods included from Functions::Nulls

#if_null, #null_if, #null_if_zero

Methods included from Functions::ExtractDatePart

#extract_day_of_month, #extract_day_of_week, #extract_day_of_year, #extract_hour, #extract_minute, #extract_month, #extract_quarter, #extract_week_of_year, #extract_year, #extract_year_month

Methods included from Functions::Dates

#adjust_week_start_day, #adjust_week_start_day?, #current_date, #current_time, #current_timestamp, #date_add, #date_data_type, #date_diff, #date_format, #date_format_sql, #date_int?, #date_lit, #date_literal, #date_time_format, #date_time_literal, #date_time_tz_format, #default_week_start_day, #timestamp_lit, #timestamp_literal, #truncate_date, #week_start_day, #week_starts_on_sunday?

Methods included from Capabilities

#supports_array_functions?, #supports_common_table_expressions?, #supports_cross_join?, #supports_full_join?, #supports_sub_queries?, #supports_table_join?, #supports_temp_tables?, #supports_window_functions?

Constructor Details

This class inherits a constructor from DWH::Adapters::Adapter

Instance Method Details

#change_current_database(catalog = nil) ⇒ Object

Changes the default database to the one specified here. will store the default db in @current_database attr.

Parameters:

  • catalog (String) (defaults to: nil) —

    new database to use



97
98
99
100
101
102
 
# File 'lib/dwh/adapters/sql_server.rb', line 97

def change_current_database(catalog = nil)
  return unless catalog && catalog != config[:database]

  @current_database = catalog
  use(catalog)
end

#connection ⇒ Object

Creates a connection to the target database and returns the connection object or self



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
# File 'lib/dwh/adapters/sql_server.rb', line 44

def connection
  return @connection if @connection

  properties = {
    host: config[:host],
    port: config[:port],
    database: config[:database],
    username: config[:username],
    password: config[:password],
    appname: config[:client_name],
    timeout: config[:query_timeout],
    azure: config[:azure]
  }.merge(extra_connection_params)

  @connection = TinyTds::Client.new(**properties)

  @connection
rescue StandardError => e
  raise ConfigError, e.message
end

#execute(sql, format: :array, retries: 0) ⇒ Array<Array>, ...

Execute sql on the target database.

Parameters:

  • sql (String) —

    actual sql

  • format (Symbol, String) (defaults to: :array) —

    return format type

    • array returns array of array
    • object returns array of Hashes
    • csv returns as csv
    • native returns the native result from any clients used
      • For example: Postgres using pg client will return PG::Result
      • Http clients will returns the HTTP response object
  • retries (Integer) (defaults to: 0) —

    number of retries in case of failure. Default is 0

Returns:

  • (Array<Array>, Hash, CSV, Native)

Raises:



170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
 
# File 'lib/dwh/adapters/sql_server.rb', line 170

def execute(sql, format: :array, retries: 0)
  result = with_debug(sql) { with_retry(retries) { connection.execute(sql) } }

  format = format.downcase if format.is_a?(String)
  case format.to_sym
  when :array
    result.to_a(as: :array, empty_sets: true, timezone: :utc)
  when :object
    result.to_a(as: :hash, empty_sets: true, timezone: :utc)
  when :csv
    result_to_csv(result)
  when :native
    result
  else
    raise UnsupportedCapability, "Unsupported format: #{format} for this #{name}"
  end
rescue TinyTds::Error => e
  raise ExecutionError, e.message
end

#execute_stream(sql, io, stats: nil, retries: 0) ⇒ IO

Execute sql and stream responses back. Data is writtent out in CSV format to the provided IO object.

Parameters:

  • sql (String) —

    actual sql

  • io (IO) —

    IO object to write records to

  • stats (StreamingStats) (defaults to: nil) —

    collect stats and preview data this is optional

  • retries (Integer) (defaults to: 0) —

    number of retries in case of failure

Returns:

  • (IO)

Raises:



191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
 
# File 'lib/dwh/adapters/sql_server.rb', line 191

def execute_stream(sql, io, stats: nil, retries: 0)
  with_debug(sql) do
    with_retry(retries) do
      result = connection.execute(sql)
      io.write(CSV.generate_line(result.fields))
      result.each(as: :array, empty_sets: true, cache_rows: false, timezone: :utc) do |row|
        stats << row unless stats.nil?
        io.write(CSV.generate_line(row))
      end
    end
  end

  io.rewind
  io
rescue StandardError => e
  raise ExecutionError, e.message
end

#extract_day_name(exp, abbreviate: false) ⇒ Object 



219
220
221
222
 
# File 'lib/dwh/adapters/sql_server.rb', line 219

def extract_day_name(exp, abbreviate: false)
  exp = cast(exp, 'date') unless exp =~ /cast/i
  super(exp, abbreviate: abbreviate).downcase
end

#extract_month_name(exp, abbreviate: false) ⇒ Object 



224
225
226
227
228
229
230
231
 
# File 'lib/dwh/adapters/sql_server.rb', line 224

def extract_month_name(exp, abbreviate: false)
  exp = cast(exp, 'date') unless exp =~ /cast/i
  if abbreviate
    "UPPER(LEFT(DATENAME(month, #{exp}), 3))"
  else
    "UPPER(DATENAME(month, #{exp}))"
  end
end

#metadata(table, **qualifiers) ⇒ DWH::Table

Get the schema structure of a given a given table_name. Pass in optional catalog and schema info.

Example: metadata("public.big_table") metadata("big_table") metadata("big_table",schema: "public")

Parameters:

  • table (String) —
    • table name
  • qualifiers (Hash) —

    a customizable set of options

Options Hash (**qualifiers):

  • :catalog (String) —

    optional catalog or equivalent name space. will be ignored if the adapter doesn't support

  • :schema (String) —

    optional schema to scope to. will be ignored if the adapter doesn't support

Returns:



140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
 
# File 'lib/dwh/adapters/sql_server.rb', line 140

def metadata(table, **qualifiers)
  db_table = Table.new table, **qualifiers
  change_current_database(db_table.catalog)

  schema_where = ''
  schema_where = "AND table_schema = '#{db_table.schema}'" if db_table.schema?

  sql = <<-SQL
              SELECT column_name, data_type, character_maximum_length, numeric_precision,numeric_scale
              FROM information_schema.columns
              WHERE table_name = '#{db_table.physical_name}'
              #{schema_where}
  SQL
  cols = execute(sql, format: 'object')
  cols.each do |col|
    db_table << Column.new(
      name: col['column_name'],
      data_type: col['data_type'],
      precision: col['numeric_precision'],
      scale: col['numeric_scale'],
      max_char_length: col['character_maximum_length']
    )
  end

  db_table
ensure
  reset_current_database
end

#reset_current_database ⇒ Object

Resets the default database to the configured one if it was changed



106
107
108
109
110
 
# File 'lib/dwh/adapters/sql_server.rb', line 106

def reset_current_database
  return if @current_database.nil? || @current_database == config[:database]

  use(config[:database])
end

#stats(table, date_column: nil, **qualifiers) ⇒ DWH::Table

Returns basic stats of a given table. Will typically include row_count, date_start, and date_end.

Examples:

stats("public.big_table", date_column: "fact_date")
stats("big_table")
stats("big_table",schema: "public")

Parameters:

  • table (String) —

    table name

  • date_column (String) (defaults to: nil) —

    optional date column to use to find range

  • qualifiers (Hash) —

    a customizable set of options

Options Hash (**qualifiers):

  • :catalog (String) —

    optional catalog or equivalent name space. will be ignored if the adapter doesn't support

  • :schema (String) —

    optional schema to scope to. will be ignored if the adapter doesn't support

Returns:

Raises:



118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
 
# File 'lib/dwh/adapters/sql_server.rb', line 118

def stats(table, date_column: nil, **qualifiers)
  change_current_database(qualifiers[:catalog])
  table_name = qualifiers[:schema] ? "#{qualifiers[:schema]}.#{table}" : table
  sql = <<-SQL
              SELECT count(*) ROW_COUNT
                  #{date_column.nil? ? nil : ", min(#{date_column}) DATE_START"}
                  #{date_column.nil? ? nil : ", max(#{date_column}) DATE_END"}
              FROM #{quote(table_name)}
  SQL

  result = connection.execute(sql)
  row = result.to_a(empty_sets: true).first
  TableStats.new(
    row_count: row['ROW_COUNT'],
    date_start: row['DATE_START'],
    date_end: row['DATE_END']
  )
ensure
  reset_current_database
end

#stream(sql) {|chunk| ... } ⇒ Object

Executes the given sql and yields the streamed results to the given block.

Parameters:

  • sql (String) —

    actual sql

Yields:

  • (chunk) —

    Yields a streamed chunk as it streams in. The chunk type might vary depending on the target db and settings

Raises:



210
211
212
213
214
215
216
217
 
# File 'lib/dwh/adapters/sql_server.rb', line 210

def stream(sql, &block)
  with_debug(sql) do
    result = connection.execute(sql)
    result.each(empty_sets: true, cache_rows: false, timezone: :utc) do |row|
      block.call(row)
    end
  end
end

#table?(table_name) ⇒ Boolean

Check if table exists in remote db.

Parameters:

  • qualifiers (Hash) —

    a customizable set of options

Returns:



113
114
115
 
# File 'lib/dwh/adapters/sql_server.rb', line 113

def table?(table_name)
  tables.include?(table_name)
end

#tables(**qualifiers) ⇒ Array<String>

Get all tables available in the target db. It will use the default catalog and schema config only specified here.

Parameters:

  • qualifiers (Hash) —

    a customizable set of options

Options Hash (**qualifiers):

  • :catalog (String) —

    optional catalog or equivalent name space. will be ignored if the adapter doesn't support

  • :schema (String) —

    optional schema to scope to. will be ignored if the adapter doesn't support

Returns:

  • (Array<String>) 


76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
 
# File 'lib/dwh/adapters/sql_server.rb', line 76

def tables(**qualifiers)
  change_current_database(qualifiers[:catalog])
  table_catalog = qualifiers[:catalog] || config[:database]
  table_schema_where = qualifiers[:schema] ? " AND table_schema = '#{qualifiers[:schema]}'" : ''

  sql = <<~SQL
    SELECT table_name
    FROM information_schema.tables
    WHERE table_catalog = '#{table_catalog}'
    #{table_schema_where}
  SQL

  res = execute(sql)
  res.flatten
ensure
  reset_current_database
end

#test_connection(raise_exception: false) ⇒ Boolean

Tests the connection to the target database and returns true if successful, or raise Exception or false connection object or self

Returns:

Raises:



66
67
68
69
70
71
72
73
 
# File 'lib/dwh/adapters/sql_server.rb', line 66

def test_connection(raise_exception: false)
  connection
  true
rescue StandardError => e
  raise ConnectionError, e.message if raise_exception

  false
end

#valid_config? ⇒ Boolean

Returns:



233
234
235
236
237
238
 
# File 'lib/dwh/adapters/sql_server.rb', line 233

def valid_config?
  super
  require 'tiny_tds'
rescue LoadError
  raise ConfigError, "Required 'tiny_tds' gem missing. Please add it to your Gemfile."
end