Как уже упоминалось ранее в другом ароматизатозе в ароматизаторе redshift AWS Redshift, разве это не то, что инструменты в Интернете трудно прийти.
Тестирующие таблицы важны в World Data World, были ли вы указываете работу? Есть ли аномалии в ваших данных?
Но таблицы могут быть большими и состоят из нескольких разных точек данных и логики. Это может означать, что для выполнения должной осмотрительности для тестирования таблицы вам может потребоваться запустить пять, десять, пятнадцать, даже двадцать отдельные проверки на столе после ее построенной. Человеческая природа означает, что это не всегда случается, и, безусловно, не последовательно через каждый стол и аналитик.
Одна из моих команд вокалачила эту проблему, и мы говорили о контрольном списке, который мы могли бы создавать, как только мы построили таблицу, чтобы узнать, что тестировать, это также будет иметь большое значение для того, чтобы помочь тестеру видеть, что было смягчено. Я играл с идеей создания некоторого плагина VSCode, который читает ваш код и генерирует контрольный список, но чем больше я смотрел в нее, тем больше разное решение имеет больше смысла и ощущалось проще.
Зачем создать контрольный список вещей для тестирования, который затем потребует бы аналитика для создания запросов испытаний, когда я могу просто прыгать прямо на построение инструмента, который генерирует тестовые запросы ??? Конечно, это не будет охватывать все сценарии, но ничто не будет, причина, по которой вы построили таблицу, может никогда не быть очевидной в коде.
Поэтому я знал, что я хотел построить, однако я знал только HTML/CSS, SQL/PLSQL, пакет и всплеск энергетической оболочки. Пока я мог бы сделать это в PLSQL или Power Shell, я видел это как отличную возможность для изучения Python.
Я кратко воздействовал на Пандас, и поэтому я был знаком с кадрами данных, знакомым для того, кто работает с данными каждый день. Несколько учебных пособий по пандам позже И у меня было общее направление.
Методология
Там, где я приземлился с точки зрения механика, было использовать таблицы определения данных языка или DDL, который является планом для того, как была создана таблица. Смотрите ниже на примере. Мой скрипт прочитал этот DDL, а затем на основе того, какой тип данных столбец был, строка, целое число, дату, будет вращаться с кучами изготовленных настроек. Итак, если столбец был датой, он выпил бы запрос, который протестировал значение MIN и максимального значения этого значения даты, что мы делаем, чтобы проверить, что у нас есть записи в правильном диапазоне дат.
Я сделал это, получив пользователя копировать DDL в буфер обмена, а затем с использованием буфера обмена копирования Pandas будет глотать DDL в скрипт.
sql_load = pd.read_clipboard(sep='\n',header=None, squeeze=1)
Затем я проверял, чтобы убедиться, что загруженный контент был действительным, первое слово, необходимое для «создания», поэтому я принудил это и вынудил ошибку, если она не нашла этого. Если он прошел тест, скрипт прочитал то, что имена таблицы так, как это будет использоваться позже.
#GET FIRST ROW OF DDL
sql_header = sql_load.iloc[0]
#VALIDATE THAT THIS IS A VALID DDL
sql_validate = re.search(r'^\w*\b',sql_header, flags=re.U)[0]
sql_validate = sql_validate.upper()
if sql_validate != 'CREATE':
filename = "script_terminated_with_error.txt"
error_output = open(filename,"w")
error_output.write("If you are reading this message, the python script has terminated. \n")
error_output.write("Reason? The first word on the clipboard wasn't CREATE.\n")
error_output.write("This means you have not copied a valid Redshift SQL Table Create Statement to your clipboard. \n")
error_output.write("For more help refer to: https://github.com/ronsoak/SQL_Test_Script_Gen.\n")
error_output.close
os.startfile(fr'{filename}')
sys.exit("Not a valid DDL, must start with CREATE")
else: sql_table = re.search(r'\w+\.\w+',sql_header, flags=re.U)[0]
Я решил, что вывод этого скрипта будет записан в текстовый файл, поэтому мне пришлось заставить Python создать текстовый файл, переименуйте его имя имени таблицы, а затем писать к нему содержимое. Но таблицы часто называют Schema.tableName, и мы не можем иметь точку в файле имени, поэтому я должен был повторно договориться о том, как все это выглядело.
sql_table_file = sql_table.replace(".","][")
filename = "[table_testing]["+sql_table_file+"].txt"
filename = filename.lower()
sql_output = open(filename,"w")
Итак, теперь у меня есть весь этот DDL, но единственное, что мне нужно, – это имена столбцов, и там типы данных, мне не нужны их типы сжатия, и мне не нужны таблицы DistStyle, Switkey или Sortkey. Так что, кажется, я использую кадрами данных, которые я проходил через процесс убрать весь DDL, чтобы просто строки, которые содержали тип данных, к которому я загрузил все возможные типы данных в список, называемых red_types.
#LIST OF DATA TYPES
red_types = ("SMALLINT|INT2|INTEGER|INT|INT4|BIGINT|INT8|DECIMAL|NUMERIC|REAL|FLOAT4|DOUBLE|DOUBLE PRECISION|FLOAT8|FLOAT|BOOL|BOOLEAN|DATE|TIMESTAMP|TIMESTAMPTZ|CHAR|CHARACTER|NCHAR|BPCHAR|VARCHAR|CHARACTER VARYING|NVARCHAR|TEXT|GEOMETRY")
#READ THE COL NAMES AND DATA TYPES
sql_load = pd.Series(sql_load)
sql_load = sql_load.str.upper()
sql_load = sql_load.str.replace(r"\(\S*\)","")
sql_reduce = sql_load.loc[(sql_load.str.contains(fr'(\b({red_types})\b)', regex=True,case=False)==True)]
sql_reduce = sql_reduce.str.split(expand=True)
sql_cols = sql_reduce.loc[:,0:1]
sql_cols = sql_cols.rename(columns = {0:'COL_NAME',1:'DATA_TYPE'})
sql_cols['COL_NAME'] = sql_cols['COL_NAME'].str.replace(',','')
sql_cols['DATA_TYPE'] = sql_cols['DATA_TYPE'].str.replace(',','')
Затем, как только я убрал кадр данных к точным строкам, которые я хотел, я создал функцию, которая написала тесты на текстовый файл, зависящий от типа данных ROWS, а затем зацикливаться через кадр данных.
#DEFINE FUNCTION FOR PRINTING
def col_func(a,b,c):
if b in red_nums:
sql_output.write("select min("+a+"), avg("+a+"), max("+a+") from "+c+"; \n \n" )
sql_output.write("select median("+a+") from "+c+"; \n \n" )
elif b in red_dates:
sql_output.write("select min("+a+"), max("+a+") from "+c+"; \n \n" )
elif b in red_string:
sql_output.write("select "+a+", count(*) from "+c+" group by 1 order by 2 desc limit 50; \n \n" )
sql_output.write("select count(distinct("+a+")), count(*) from "+c+" limit 50; \n \n" )
elif b in red_bool:
sql_output.write("select "+a+", count(*) from "+c+" group by 1 order by 2 desc limit 10; \n \n" )
elif b in red_geo:
sql_output.write("Geospatial Data not currently supported. \n \n")
else:
sql_output.write("Column:"+a+"is not a know Datatype. Datatype passed was:"+b+"\n \n")
for index, row in sql_cols.iterrows():
col_func(row['COL_NAME'], row['DATA_TYPE'], sql_table)
Наконец, после того как скрипт был сделан, я завернул пакетный скрипт вокруг него, чтобы пользователь мог просто дважды щелкнуть скрипт, и им будет предложено скопировать DDL в их буфер обмена, а затем они могли бы предложить сценарий пакетного скрипта, чтобы выпустить скрипт Python.
Результаты
После того, как у меня было его и бегу, я дал его моей команде, чтобы использовать и тестировать. Мы столкнулись с несколькими проблемами сразу, подобно Regex, используемое для обнаружения названий таблиц, необходимых нескольких итераций. И типы данных, имеющие параметры после них, как Varchar (300), продолжали вызывать проблемы. Это также сложно отказаться от того, если неправильная вещь была скопирована в буфер обмена (как файл).
После месяца тестирования я продемонстрировал его другим аналитикам и получил их, используя его. Сейчас он бросил сообщение об ошибке, но не конец света для моей первой автоматизации Python.
Учащиеся
Я знал, когда я сделал скрипт, что он был закончен спроектирован и слишком сильно полагался на внешние библиотеки и хакеты. Заголовок моего скрипта выглядел так.
#IMPORTS
import pandas as pd
import re
import warnings
import sys
import os
#SUPRESS WARNINGS
warnings.filterwarnings("ignore", 'This pattern has match groups')
Я разместил скрипт на нити Reddit, ищете обратную связь, и довольно кратко сказали, что я не должен использовать панды для этого, и если бы я собирался использовать панды, чтобы не использовать в нем Itter.
Я знал, что могу сделать лучше.
Поэтому я отправился, чтобы восстановить этот сценарий, не использующий панды, на самом деле как можно меньше внешних библиотек, было моей целью. Поэтому я починул свои списки, кортежи и диктографии и получил кодирование.
Методология
Не используя Pandas означает, что я не мог скопировать DDL от буфера обмена, родной Python не может этого сделать, другие библиотеки могут Но я решил, что DDL может быть в текстовом файле и так загружен. Так Первое, что пакетный файл теперь открывает текстовый файл и получает пользователю сохранять в нее DDL.
Так что теперь я заблокировал нагрузку DDL к текстовому файлу. Следующие несколько шагов одинаковы. Прочитайте имя таблицы и подготовьте выходной текстовый файл, и это имя файла.
Отметим, что убрал проверку, которая проверяет, что первое слово «Создание» аналитик будет понять это достаточно скоро.
#LOAD THE DDL
load_file = open('test_script_input.sql','r')
file_contents = load_file.readlines()
#GET THE TABLE NAME
table_head = file_contents[0:1]
table_name = re.search(r'\w+\.\w+',str(table_head), flags=re.U)
table_name = table_name.group(0)
table_name = str(table_name)
#FILE OUTPUT
sql_table_file = table_name.replace(".","][")
filename = "[table_testing]["+sql_table_file+"].txt"
filename = filename.lower()
sql_output = open(filename,"w")
Таким образом, никаких кадров данных на этот раз правильно? Это означает, что DDL был загружен в список. Как я уточнил этот список только к правильным столбцам? В прошлый раз занял 15 строк кода, на этот раз все это сделано в одной линии лямбда.
#RESTRICT TO APPLICABLE COLUMNS valid_rows = list(filter(lambda x: re.search(r'\b(SMALLINT|INT2|INTEGER|INT|INT4|BIGINT|INT8|DECIMAL|NUMERIC|REAL|FLOAT4|DOUBLE|DOUBLE PRECISION|FLOAT8|FLOAT|BOOL|BOOLEAN|DATE|TIMESTAMP|TIMESTAMPTZ|CHAR|CHARACTER|NCHAR|BPCHAR|VARCHAR|CHARACTER VARYING|NVARCHAR|TEXT|GEOMETRY)',x,flags=re.IGNORECASE),file_contents))
Методология одинакова, ища записи в списке, которые содержат только тип данных Redshift, но это было значительно более элегантно, мне даже не нужно иметь типы данных, загруженные в отдельный список. Он также может обрабатывать полные имена типов данных, где до того, как мой скрипт пошел к усилиям, чтобы сначала снизить десятичное (50,5) до десятичного десятичного периода, то он обнаружил, что в этом скрипте он может обнаружить типы данных без необходимости сначала Снимите кронштейны.
Следующие шаги очень похожи. Теперь у меня есть мои действительные ряды, мне нужно было преобразовать некоторые значения на строки, а вместо того, чтобы пройти фактические типы данных в функцию, которую я передал тип высокого уровня. Итак, где это был int, Bigint, десятичный, я передал его как «номер».
#CREATE TEST SCRIPT
for table_line in valid_rows:
table_line = str(table_line)
col_name = table_line.split()[0:1]
col_name = str(col_name)[1:-1]
col_name = col_name.replace("'","")
col_name = col_name.replace("(","")
col_name = col_name.replace(")","")
dat_type = table_line.split()[1:2]
dat_type = str(dat_type)
if (re.search(r'\b(CHAR|CHARACTER|NCHAR|BPCHAR|VARCHAR|CHARACTER VARYING|NVARCHAR|TEXT)',dat_type,flags=re.IGNORECASE)): col_type= 'STRING'
elif (re.search(r'\b(SMALLINT|INT2|INTEGER|INT|INT4|BIGINT|INT8|DECIMAL|NUMERIC|REAL|FLOAT4|DOUBLE|DOUBLE PRECISION|FLOAT8|FLOAT)',dat_type,flags=re.IGNORECASE)): col_type= 'NUMBER'
elif (re.search(r'\b(BOOL|BOOLEAN)',dat_type,flags=re.IGNORECASE)): col_type = 'BOOL'
elif (re.search(r'\b(DATE|TIMESTAMP|TIMESTAMPTZ)',dat_type,flags=re.IGNORECASE)): col_type = 'DATES'
elif (re.search(r'\b(GEOMETRY)',dat_type,flags=re.IGNORECASE)): col_type = 'GEO'
else: col_type= 'BAD'
col_type = str(col_type)
script_gen(col_name,col_type,table_name)
В предыдущей версии я сгенерировал один или два тестовых запроса на тип столбца. На этот раз я хотел пойти дальше, и поэтому я подумал о более сценариях, которые мы хотели бы проверить, как если бы это число, проверку 25, 50 и 75 квартилей. Я даже добавил заголовки, чтобы дать каждую структуру области, пока до этого был текстовый файл с номерами. Излишне говорить, что моя функция была намного больше.
#SCRIPT_GEN FUNCTION
def script_gen(a,b,c):
if b == 'NUMBER':
sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
sql_output.write("-- Testing for Column: "+a+"\n")
sql_output.write("-- Column Type: Number \n")
sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
sql_output.write("-- Testing counts of column \n")
sql_output.write("select count(*) as row_count, count(distinct("+a+")) as distinct_values from "+c+"; \n \n" )
sql_output.write("-- Checking for nulls, are you expecting nulls? \n")
sql_output.write("select count(*) from "+c+" where "+a+" is null; \n \n" )
sql_output.write("-- Testing mins, avgs and max values \n")
sql_output.write("select min("+a+"), avg("+a+"), max("+a+") from "+c+"; \n \n" )
sql_output.write("-- Testing median, redshift doesn't like doing medians with other calcs \n")
sql_output.write("select median("+a+") from "+c+"; \n \n" )
sql_output.write("-- Testing 25% quartile, can be slow \n")
sql_output.write("select percentile_cont(.25) within group (order by"+a+") as low_quartile from "+c+"; \n \n" )
sql_output.write("-- Testing 50% quartile, can be slow\n")
sql_output.write("select percentile_cont(.50) within group (order by"+a+") as mid_quartile from "+c+"; \n \n" )
sql_output.write("-- Testing 75% quartile, can be slow\n")
sql_output.write("select percentile_cont(.75) within group (order by"+a+") as high_quartile from "+c+"; \n \n" )
elif b == 'DATES':
sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
sql_output.write("-- Testing for Column: "+a+"\n")
sql_output.write("-- Column Type: Date \n")
sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
sql_output.write("-- Testing counts of column \n")
sql_output.write("select count(*) as row_count, count(distinct("+a+")) as distinct_values from "+c+"; \n \n" )
sql_output.write("-- Checking for nulls, are you expecting nulls? \n")
sql_output.write("select count(*) from "+c+" where "+a+" is null; \n \n" )
sql_output.write("-- Checking for highs and lows, are they as you expected? \n")
sql_output.write("select min("+a+"), max("+a+") from "+c+"; \n \n" )
sql_output.write("-- Checking how many dates are in the future. \n")
sql_output.write("select count(*) from "+c+" where "+a+" >sysdate; \n \n" )
sql_output.write("-- Checking how many dates have a timestamp. \n")
sql_output.write("select count(*) from "+c+" where substring("+a+",12,8)<> '00:00:00'; \n \n" )
elif b == 'STRING':
sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
sql_output.write("-- Testing for Column: "+a+"\n")
sql_output.write("-- Column Type: String \n")
sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
sql_output.write("-- Testing counts of column \n")
sql_output.write("select count(*) as row_count, count(distinct("+a+")) as distinct_values from "+c+"; \n \n" )
sql_output.write("-- Checking for nulls, are you expecting nulls? \n")
sql_output.write("select count(*) from "+c+" where "+a+" is null; \n \n" )
sql_output.write("-- Top 50 values \n")
sql_output.write("select "+a+", count(*) from "+c+" group by 1 order by 2 desc limit 10; \n \n" )
sql_output.write("-- Check string lengths \n")
sql_output.write("select min(len("+a+")) as min_length,max(len("+a+")) as max_length from "+c+"; \n \n" )
elif b == 'BOOL':
sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
sql_output.write("-- Testing for Column: "+a+"\n")
sql_output.write("-- Column Type: BOOL \n")
sql_output.write("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- \n")
sql_output.write("-- Testing counts of column \n")
sql_output.write("select count(*) as row_count, count(distinct("+a+")) as distinct_values from "+c+"; \n \n" )
sql_output.write("-- Checking for nulls, are you expecting nulls? \n")
sql_output.write("select count(*) from "+c+" where "+a+" is null; \n \n" )
sql_output.write("-- Breakdown of boolean \n")
sql_output.write("select "+a+", count(*) from "+c+" group by 1 order by 2 desc limit 10; \n \n" )
elif b == 'GEO':
sql_output.write("Geospatial Data not currently supported. Suggest Something? \n \n")
else:
sql_output.write("Column:"+a+"is not a know Datatype. Datatype passed was:"+b+"\n \n")
И вуаля, мы получаем это:
Результаты
Этот скрипт гораздо более «чистым» в моих глазах, не импортирует чрезмерное количество библиотек и определенно без подавленных предупреждений. Он может обрабатывать нюансы в другом коде намного лучше, чем предыдущий. Не имеет значения, является ли это varchar (15) или varchar (400000), он обрабатывает как правильно.
Конечно, я сделал несколько компромиссов, таких как не проверяя входной вход или позволяя пользователю просто скопировать DDL в буфер обмена, он даже не открывает тестовый скрипт для них больше, но все это очень незначительные вещи Это вряд ли влияет на аналитик.
Что делать в v3
Первоначально я хотел загрузить GUI для загрузки DDL, где окно ввода откроется, и пользователь будет вставить там DDL. Не мог сэкономить умственную энергию, чтобы узнать TKinter на этот раз, но определенно что-то изучить.
Еще одна вещь, которую я представлял, когда решил восстановить, это то, что это фактически отправит запросы к нашему красному образованию, запустите их и вернуть результаты. Опять расти больше, чем я могу жевать этот проход, прежде всего, потому что у меня нет разрешения на установку почтогресного инструмента командной строки, необходимое для отправки команд на Redshift на работе, так что я посмотрю на это еще один день.
ronsoak/sql_test_script_gen.
Приложение Python для генерации сценариев тестирования SQL в зависимости от таблицы DDL.
Для красной таблицы сборки
Что это?
Это сценарий Python, который читает сборку таблицы, написанную в синтаксисе Redshift, а затем для каждого столбца выводит текстовый файл с некоторыми основными сценариями испытаний.
Почему?
Часть тщательного тестирования в SQL является обеспечение того, чтобы каждый столбец, который вы потянули, работает, как и ожидалось, этот скрипт быстро создает основные тесты, которые вы должны запустить, оставив вас, чтобы написать более персонализированные тесты.
До реквизиты
- Последняя версия Python 3.0+
- Таблицы, построенные с помощью Redshift, не будут (в настоящее время) работают на таблицах, встроенных в Oracle, TSQL, PostgreSQL, MySQL и т. Д…..
Установка/конфигурация
- Загрузите Git как ZIP, вам действительно нужно только «Launch_me.bat» и ‘gen_test_script.py’
- Поместите эти два файла в папку по вашему выбору
- Изменить ‘launch_me.bat’ и перейдите к строке 11
- Редактировать ‘C:/Путь к вашему python установить/python.exe ‘быть там, где ваш python.exe установлен
- Редактировать «C:/путь к этому сценарию/gen_test_script.py», чтобы быть …
Напоминание: все взгляды, выраженные здесь, являются моими собственными и не представляют моего работодателя, Xero.
Кто я?
Вы должны читать ….
WHELP, они получили все наши данные, теперь что? – Руководство, ну лекция сначала, затем руководство.
ronsoaak · 9 · 18 мин прочитаны
Оригинал: “https://dev.to/ronsoak/i-built-my-own-sql-tester-in-python-then-rebuilt-it-again-from-scratch-here-s-what-i-learned-c9l”