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 sysadmin, severadmin 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
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:
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.
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
Komentarz do “MSSQL – pobieranie danych ze stron web (Transact-SQL)”
Super poradnik 😉 Tego szukałem!