MSSQL – pobieranie danych ze stron web (Transact-SQL)

W jaki sposób pobierać dane ze stron sieci web do SQL Server?

Dziś naszym zadaniem będzie pobranie danych ze stron www i wrzucenie ich kodu bezpośrednio do tabeli w SQL Server. W jaki sposób to zrobić i  z jakich funkcjonalności należy skorzystać? To, co powinieneś posiadać to dostęp do bazy master. Jeśli nie posiadasz odpowiedniej roli, by to zrobić skontaktuj się z adminem serwera. Przykładowe role, które gwarantują dostęp do tej bazy to np sysadminseveradmin lub przynajmniej grant execute na bazie master. Potrzebny będzie także powershell.

Po co robić takie rzeczy? Na przykład po to, by ściągnąć interesujące nas dane z wybranej strony web lub wybranych podstron. W rozbudowanej wersji możliwe jest także stworzenie na tej podstawie bota chodzącego po stronach i indeksującego interesujące nas dane.

 

Wgrywanie kodu strony www przez MSSQL – od czego zacząć?

Jak wcześniej wspomniałem do pobierania danych ze stron www będzie wykorzystywane połączenie z użyciem Powershell. By umożliwić uruchamianie i wykonywanie komend Powershella przez T-SQL powinniśmy zacząć od prostej konfiguracji naszej bazy, która odblokuje nam funkcjonalność xp_cmdshell. By to zrobić, uruchom poniższy kod na bazie master.

--tomaszkenig.pl
use master;
go
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
RECONFIGURE;  
GO
Skrypty SQL Server - Pobieranie danych stron www do SQL Server
Skrypty SQL Server – Pobieranie danych stron www do SQL Server

Ok, baza jest już skonfigurowana do współpracy z Powershell. Jedyne, co pozostało nam do zrobienia, to napisać dobry kod, który będzie ściągał dane z dowolnej strony web do SQL Server.

 

Przykład pobierania danych stron sieci web

Pora na przykład. Funkcjonalność obsługi Powershell jest już aktywna. Na samym początku warto uruchomić sam PS, by zobaczyć, co tak dokładnie zostanie uruchomione. Uruchom go wpisując Powershell w menu uruchom Windows. Następnie wklej kod i odpal go.

--tomaszkenig.pl
$ws=(New-Object System.Net.WebClient) ; $ws.Encoding = [System.Text.Encoding]::UTF8 ;$ws.Downloadstring('https://tomaszkenig.pl')

Wynik powinien zawierać źródło strony. Zapewne widoczne tagi html są Ci znajome:

Skrypty SQL Server - Pobieranie danych stron www . Transact SQL.
Skrypty SQL Server – Pobieranie danych stron www . Transact SQL.

 

Przykład: Pobieranie danych stron sieci web do MSSQL

Pora na przykład z włączeniem SQL Server w pobieranie danych. Stwórzmy prostą aplikację. Naszym zadaniem jest napisanie kodu w Transact-SQL, którego zadaniem będzie:

  • połączenie się ze stroną www
  • pobranie danych
  • insert danych do zmiennej o typie table(varchar(max))
  • wyświetlenie pobranych danych

Poniżej kod programu:

--tomaszkenig.pl
DECLARE @CMD      NVARCHAR(800);
DECLARE @OUTPUT  TABLE(col1 NVARCHAR(max));
DECLARE @URL      NVARCHAR(max);

SET @URL= 'https://tomaszkenig.pl/'
SET @CMD = 'powershell "$ws=(New-Object System.Net.WebClient) ; $ws.Encoding = [System.Text.Encoding]::UTF8 ;$ws.Downloadstring('''+@URL+''')"'
insert into @OUTPUT EXEC master.dbo.xp_cmdshell @CMD 
select * from @OUTPUT

Jak widzisz, utworzone zostały 3 zmienne dla polecenia PS, adresu URL oraz zmienna output będąca tabelą, do której wgrywane są dane. Dane w tym wypadku insertujemy za pomocą polecenia Insert, a nie set, gdyż jak wspomniałem jest to tabela. Odczyt danych także w tym wypadku wykonujemy za pomocą select, a nie print.

Skrypty SQL Server - Pobieranie danych stron www . MSSQL.
Skrypty SQL Server – Pobieranie danych stron www . MSSQL.

Jeśli chcesz, by dane były insertowane bezpośrednio do rzeczywistej tabeli w bazie MSSQL, a nie do tabeli będącej zmienną, możesz zastosować poniższy kod tworzący taką tabelę. Oczywiście powinieneś też dokonać zmian w samym skrypcie.

--tomaszkenig.pl
Create table tabWebpage (webdata varchar(max));

 

Możliwości rozbudowy programu

Program możesz oczywiście rozbudować. To, co można zrobić na szybko, to m.in:

  • Utworzenie procedury składowanej z jobem
  • Zapętlanie programu, by pobierał podstrony
  • Zapętlenie pobierania danych, tak by wynikiem był jeden rekord, a nie zbiór rekordów itp.

Jeśli chcesz poszerzyć swoją wiedzę na temat SQL Server, zachęcam do zapoznania się z kursem SQL Server, który zamieszczam pod tym adresem: Kurs MsSQL. Odsyłam także do innych przykładów dla SQL Server

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Komentarz do “MSSQL – pobieranie danych ze stron web (Transact-SQL)”