Webscrape -> SQL
Jeg har efterhånden prøvet mange forskellige metoder til at importere data i SQL Serveren, fra Excel, CSV, XML, TXT osv. Senest lød opgaven ”indlæs data fra en hjemmeside, som opdateres ugentligt”. Der er flere forskellige veje til at løse denne opgave. Jeg valgte at benytte Python, som fra 2017 er en mere integreret del af SQL Serveren hvor vi har mulighed for at køre et Python script direkte fra SQL Query og få data retur som tabel, ret lækkert.
Vi har en Stored Procedure sp_execute_external_script, som står for eksekveringen af selve scriptet.
Syntax
sp_execute_external_script
@language = N'language',
@script = N'script'
[ , @input_data_1 = N'input_data_1' ]
[ , @input_data_1_name = N'input_data_1_name' ]
[ , @input_data_1_order_by_columns = N'input_data_1_order_by_columns' ]
[ , @input_data_1_partition_by_columns = N'input_data_1_partition_by_columns' ]
[ , @output_data_1_name = N'output_data_1_name' ]
[ , @parallel = 0 | 1 ]
[ , @params = N'@parameter_name data_type [ OUT | OUTPUT ] [ ,...n ]' ]
[ , @parameter1 = 'value1' [ OUT | OUTPUT ] [ ,...n ] ]Python er en del af SQL Serverens Machine Learning, hvilket også betyder at Machine Learning skal være installeret sammen med SQL Serveren og at Python også skal være valgt under installationen. (jeg skriver en artikel om mine erfaringer og udfordringer med Python og SQL snarest).
Python
Først skal vi have styr på scriptet. Python er utroligt stærkt og har nogle fede ”moduler” at arbejde med, og man kommer ikke uden om Pandas, når vi snakker data og Python. Pandas giver os et væld af muligheder for at hente, skrive og manipulere data fra forskellige kilder.
En af import mulighederne er pandas.read_html() hvor pandas selv sørger for at læse sitet igennem og importere alle <table> tags (og det kan selvfølgelig konfigureres) og det virker utrolig godt og hurtigt.
Syntax
pandas.read_html(io, match='.+', flavor=None, header=None, index_col=None, skiprows=None, attrs=None, parse_dates=False, thousands=',', encoding=None, decimal='.', converters=None, na_values=None, keep_default_na=True, displayed_only=True)https://pandas.pydata.org/docs/reference/api/pandas.read_html.html
Lad os tage et eksempel:
På sqlserverbuilds.com er en liste over SQL Server versioner og hvilke opdateringer der er frigivet til dem, samt hvad de fikser og tilføjer:
https://sqlserverbuilds.blogspot.com/
For at scrape denne side kan vi altså benytte Pandas, ret simpelt ser det sådan her ud:
import pandas as pd
#new dataframe from html
df = pd.read_html("https://sqlserverbuilds.blogspot.com/")
#take 3. <table> from dataframe
print(df[3])Og når jeg kører scriptet, så printes resultatet ud i vinduet (jeg benytter Visual Studio Code til at skrive scripts i). Bemærk at Pandas default er sat op til at printe et mindre resultatsæt end det fulde. Dette kan også konfigureres så alt vises, men dette er fint nok for at se hvad der sker:
Det syntes jeg er ret lækkert og lækkert at arbejde med. Pandas og Python giver os generelt muligheden for at bearbejde data yderligere, men lad os blive ved dette script.
SQL
Først skal vi lige sikre at vi har enabled eksekveringen af external scripts.
I et Query vindue i SSMS kan vi køre følgende kommando:
sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;
Så er external scripts enabled… I hvert fald på SQL Server 2019, for SQL Server 2017 skal vi lige genstarte servicen før det slår igennem.
Nu kan vi eksekvere samme pythonscript som før bare fra et SQL Server Query vindue:
I Query vinduet skriver jeg:
EXEC sp_execute_external_script
@language = N'Python',@script = N'import pandas as pd
#new dataframe from html
df = pd.read_html("https://sqlserverbuilds.blogspot.com/")
#take 3. <table> from dataframe
print(df[3])'Som giver mig dette resultat:
Det kan godt tage et par sekunder for SQL Serveren at få startet op første gang du kører et Python script herfra.
Men det er ret cool, at med relativt få linjer, har vi taget data fra en hjemmeside via et script kørt af SQL Serveren, og har resultatet stående i ”message” vinduet. Så lad os gå skridtet videre og lave det til et reelt datasæt vi kan arbejde videre med i SQL Serveren.
Først skal vi definere den tabel data skal indsættes i, her er det vigtigt at have det samme kolonneantal og datatyper som Python scriptet generer.
DROP TABLE IF EXISTS #sqlbuilds
CREATE TABLE #sqlbuilds(
Build VARCHAR(50),
Alternative_builds VARCHAR(50),
File_version VARCHAR(50),
Q VARCHAR(50),
KB VARCHAR(50),
KB_Description VARCHAR(250),
Release_Date VARCHAR(50)
)(ja, jeg har taget den lette vej her og lavet rene string kolonner 🙈😂)
Nu skal vi lave 2 ændringer til vores script:
INSERT INTO #sqlbuilds (Build,Alternative_builds,File_version,Q,KB,KB_Description,Release_Date)
EXEC sp_execute_external_script
@language = N'Python',@script = N'import pandas as pd
#new dataframe from html
df = pd.read_html("https://sqlserverbuilds.blogspot.com/")
#take 3. <table> from dataframe
OutputDataSet = df[3]'Der skal tilføjes Insert into i toppen, og vigtigt i bunden i stedet for print (df[3]) skal det ændres til OutputDataSet som er en variabel vi kan arbejde med når der skal returneres data fra pythonscripts eksekveret fra SQL Servere.
Herefter har vi data liggende i vores temp tabel:
Bemærk at række 1 indeholder header rows, dette fikser vi let ved at angive at første række er header:
Og nu er data rigtig fint ud:
En lille detalje; det er vigtigt at angive OutputDataSet med de store bogstaver for denne variabel er:
casesensitive (det er python) så Outputdataset=df[3] giver en SQL fejl
Msg 11536, Level 16, State 3, Line 13
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.Jeg skriver snart en artikel med mine erfaringer med installation, opsætning og udfordringer som jeg har oplevet med Python fra SQL Server.
Tak fordi du læste med. Som altid så er du velkommen til at kontakte mig eller en af mine dygtige kollegaer i Unit IT på tlf.: 88 333 333, hvis du har spørgsmål eller udfordringer med dit SQL miljø.
