def show_sql(self, pretty_print=False, **params): q = self._get_query() sql = VulknSQLFormatter().format(q, **params) if params else q statements = vulkn.sql.SQLMessage(sql).statements() if len(statements) > 1: raise Exception('More than one statement is not supported') return sqlformat(statements[0].optimize(), oneline=not pretty_print)
def _q(self, query: str, settings: dict = None) -> dict: if self._http is None: http_kwargs = {} if self._auth['insecure']: http_kwargs['cert_reqs'] = 'CERT_NONE' requests.packages.urllib3.disable_warnings() log.debug(http_kwargs) self._http = urllib3.PoolManager(**http_kwargs) host = self._auth.get('host') if not host.startswith('http'): host = f'http://{host}' port = self._auth.get('http_port') or self._auth.get('port') or '8123' log.log(LogLevels.SQL, sqlformat(query)) headers = { 'X-ClickHouse-User': self._auth.get('user'), 'X-ClickHouse-Key': self._auth.get('password') } body = f'{query}' if (query.strip().lower().startswith('select') or query.strip().lower().startswith('with') or query.strip().lower().startswith('show')): body = f'{body} FORMAT TSVWithNamesAndTypes' s = '' if not settings else '&'.join( [f'{k}={i}'.format(k, i) for k, i in settings.items()]) if s != '': log.log(LogLevels.SQL, s) r = self._http.request('POST', f'{host}:{port}/?{s}', headers=headers, body=body) return r
def marshal_columns(src_database, src_table, target_database, target_table, columns, types): cols = [] for i, t in enumerate(types): c = 'nullIf(trim(toString("{}")), \'\')'.format(columns[i]) if t in ('DateTime', 'Date'): c = 'parseDateTimeBestEffortOrNull({})'.format(c) cols.append(c) columns = ','.join(cols) database = target_database table = target_table sql = f'INSERT INTO {database}.{table} SELECT {columns} FROM {src_database}.{src_table}' return sqlformat(sql)
def _q(self, cli, query, settings=None): if cli is None: flag_opts_overrides = {'disable_suggestion': True} opts_overrides = {'format': 'TSVWithNamesAndTypes'} cli = self._cli(flag_opts_overrides=flag_opts_overrides, opts_overrides=opts_overrides, settings_overrides=settings) log.log(5, quote_shlex(cli)) log.log(LogLevels.SQL, sqlformat(query)) p = subprocess.run(cli, input=query, stdout=subprocess.PIPE, universal_newlines=True) return p
def infer_column_type(database, table, column, sample=0): sample = 0 sql = """ SELECT '{column}' AS col_name, any(nullable) AS nullable, (uniq_approx < 6 AND recommended_type = 'String') AS enum, (uniq_approx > 10 AND uniq_approx < 1000000 AND recommended_type = 'String') AS low_cardinality, recommended_type, any(sample) AS sample, any(uniq_approx) AS uniq_approx FROM ( SELECT (null_cnt > 0) AS nullable, multiIf( uint_cnt = non_null_cnt, multiIf( length(toString(toUInt8OrNull(col))) = length(col), 'UInt8', length(toString(toUInt16OrNull(col))) = length(col), 'UInt16', length(toString(toUInt32OrNull(col))) = length(col), 'UInt32', length(toString(toUInt64OrNull(col))) = length(col), 'UInt64', 'String'), int_cnt = non_null_cnt, multiIf( length(toString(toInt8OrNull(col))) = length(col), 'Int8', length(toString(toInt16OrNull(col))) = length(col), 'Int16', length(toString(toInt32OrNull(col))) = length(col), 'Int32', length(toString(toInt64OrNull(col))) = length(col), 'Int64', 'String'), float_cnt = non_null_cnt, multiIf( length(toString(toFloat32OrNull(col))) = length(col), 'Float32', length(toString(toFloat64OrNull(col))) = length(col), 'Float64', 'String'), datetime_cnt = non_null_cnt, if(parseDateTimeBestEffortOrNull(col) IS NOT NULL, 'DateTime', 'String'), date_cnt = non_null_cnt, if(parseDateTimeBestEffortOrNull(col) IS NOT NULL, 'Date', 'String'), 'String') AS recommended_type, sample, if({sample} == 0, any(uniq_values), any(toUInt64(uniq_values*(100/{sample})))) AS uniq_approx FROM ( SELECT '{column}' AS col_name, toString("{column}") AS col FROM {database}.{table_name} WHERE if({sample} == 0, 1, rand(1)%100 <= ({sample} - 1)) ) ALL INNER JOIN ( SELECT col_name, count() AS col_cnt, countIf(col IS NULL) AS null_cnt, col_cnt - null_cnt AS non_null_cnt, sum(match(col, '^([\-0-9\.]*)$')) AS float_cnt, sum(match(col, '^([\-0-9]*)$')) AS int_cnt, sum(match(col, '^([0-9]*)$')) AS uint_cnt, sum(match(col, '.*([0-9]{{2}}:[0-9]{{2}}).*')) AS datetime_cnt, sum(match(col, '.*([0-9]{{2}}[/\-][0-9]{{2}}[/\-][0-9]{{2}}).*')) AS date_cnt, uniqExact(col) AS uniq_values, topK(10)(col) AS sample FROM ( SELECT '{column}' AS col_name, nullIf(trim(toString("{column}")), '') AS col FROM {database}.{table_name} WHERE if({sample} == 0, 1, rand(1)%100 <= ({sample} - 1)) ) GROUP BY col_name ) USING (col_name) GROUP BY nullable, recommended_type, sample ) GROUP BY recommended_type, col_name ORDER BY recommended_type = 'UInt64' DESC , recommended_type = 'UInt32' DESC , recommended_type = 'UInt16' DESC , recommended_type = 'UInt8' DESC , recommended_type = 'Int64' DESC , recommended_type = 'Int32' DESC , recommended_type = 'Int16' DESC , recommended_type = 'Int8' DESC , recommended_type = 'Float64' DESC , recommended_type = 'Float32' DESC , recommended_type = 'DateTime' DESC , recommended_type = 'Date' DESC , recommended_type = 'String' DESC LIMIT 1 """ return sqlformat( sql.format(database=database, column=column, table_name=table, sample=sample))
def getCreate(self, table): return sqlformat(self.q('SHOW CREATE {}'.format( self._set_db(table))).e().to_records()[0]['statement'], oneline=False)