Import danych w formacie JSON z pliku do baz danych SQL

Wiktor Susfał Archiwizacja Danych, Łączność i Zarządzanie Tagi: ,
Import Danych JSON z pliku do baz SQL

Ten wpis zawiera kompletny opis rozwiązania umożliwiającego import danych offline w formacie JSON do baz danych SQL Server za pomocą oprogramowania SCADA ICONICS.

Ogólny opis rozwiązania

Tak jak zostało to ujęte we wstępie, przedmiotem tego wpisu jest przedstawianie rozwiązania importu danych offline. Posiadane dane zapisywane są w formacie JSON w pliku z takim samym rozszerzeniem. Następnie, po wypełnieniu pliku określoną ilością danych, jest on przekazywany do określonej lokalizacji. Powyższe działania mogą wykonywać się cyklicznie, co więcej – z różną częstością. Jedynym ograniczeniem jest tu konieczność podmieniania pliku na nowy, o takiej samej nazwie.

Z drugiej strony, system SCADA skonfigurowano tak, aby reagował na każdą zmianę dotyczącą omawianego pliku w danej lokalizacji. Rozwiązanie prezentowane w tym wpisie wykorzystuje kilka narzędzi składowych oprogramowania GENESIS64. Wymieniono je poniżej.

  • Web Services (Usługi Sieciowe) – nowa usługa sieciowa jest konfigurowana w celu pobrania danych JSON z określonej lokalizacji sieciowej.
  • BI-Analytix – narzędzie do wykonywania operacji na zbiorach danych użyto, aby wydobyć wartości zmiennych z obiektów JSON oraz pogrupować je jako rekordy tabeli.
  • GridWorX Server & Viewer – ten komponent posłużył zdefiniowaniu właściwych operacji na bazie danych SQL. Następnie, operacje te wykorzystuje się przy imporcie danych do bazy oraz wizualizacji.
  • BridgeWorX64 – narzędzie to pozwoliło na zbudowanie pełnego algorytmu realizującego przepływ danych.
  • System SCADA „dowiaduje się” o zmianach wprowadzonych do pliku JSON za pomocą specjalnego, natywnego trigger’a.

Podsumowując, po podmianie pliku JSON we właściwej lokalizacji, odpowiedni trigger powoduje pobranie i przetworzenie danych za pomocą WebServices oraz BI-Analytix. Ten sam trigger wyzwala również działanie odpowiedniego algorytmu (transakcji) w BridgeWorX. Algorytm ten pobiera nowo przetworzone informacje z BI-AnalytiX oraz, wykorzystując operacje zdefiniowane dzięki GridWorX, zapisuje je do baz SQL.

Schemat Importu JSON do baz SQL
Schemat Importu danych JSON do baz SQL

Import danych JSON do baz SQL może odbywać się z dodaniem dowolnej liczby kolumn o dowolnej zawartości, do każdego rekordu. Posiadając wartości zmiennych w bazie danych, można je potem zwizualizować za pomocą narzędzia GridWorX Viewer będącego częścią systemu GENESIS64.

WebServices – wczytanie danych JSON z pliku

W module Workbench użytkownik może skonfigurować połączenie z danymi pochodzącymi z różnych źródeł. Jedno z dostępnych narzędzi to „WebServices” czyli mechanizmy realizujące określone funkcje w lokalizacjach sieciowych.

GENESIS64 udostępnia dwie opcje konfiguracyjne tego modułu: pierwsza bazuje na standardzie REST, druga – SOAP. W tym przypadku wybrano pierwsza możliwość.

Konfigurując daną usługę typu WebService należy podać kilka podstawowych parametrów. Należą do nich m.in:

  • bazowy adres URL dostępu do obiektów, na których wykonywane są operacje;
  • (opcjonalnie) metoda uwierzytelniania mająca na celu zabezpieczenie operacji sieciowych;
  • górny limit dla wywołań metod zgrupowanych pod daną usługą. Limit ten dotyczy liczby wywołań metod danej usługi w zadanym odstępie czasu.

Następnie należy dodać metodę do utworzonej usługi WebService. Użytkownik powinien zdefiniować względną ścieżkę dostępu do pożądanego zasobu w sieci, określić rodzaj metody HTTP wywoływanej nad obiektem oraz sposób cyklicznego wznawiania operacji. Metoda może być wywoływana cyklicznie, lub za pomocą trigger’ów. Z uwagi na fakt, że dane są wczytywane po każdej podmianie pliku, wybrano tu specjalny trigger. Jego działanie opisano pod koniec tego artykułu.

W celu lepszego zrozumienia powyższych treści, zamieszczono zdjęcia pokazujące konfigurację WebService oraz przyporządkowanej do niego metody.

BI-Analytix – przetwarzanie danych JSON

Wstęp do BI-AnalytiX

Jak wspomniano we wstępie artykułu, BI-AnalytiX to narzędzie służące do operacji na dużych zbiorach danych. Zawiera ono wiele funkcji przydatnych przy grupowaniu informacji w tabele oraz nadawaniu tabelom hierarchii. Dlatego, że nie jest to wpis poświęcony obsłudze tego narzędzia, zamieszczono link, pod którym znajduje się więcej informacji o BI-AnalytiX.

Operacje na pliku JSON

W tym przypadku BI-AnalytiX posłużył do pogrupowania danych w tabele oraz wydzielenia poszczególnych wartości z obiektów JSON. Struktura wiadomości zapisywanych w pliku wygląda następująco.

{„PE”:128,”TIM”:”2015-12-01T10:21:59+01:00″,”Record”:[[0,0,7,4095],[0,0,11,8],[0,1,7,4095],[0,1,11,8],[0,2,7,4095],[0,2,11,8],[0,3,7,4095],[0,3,11,8]]}

Wyróżnić tu można między innymi datę zapisu wartości zmiennych (TIM) oraz zbiór wektorów (RECORD) zawierający właściwe dane. Aby infromacje te nadawały się do wizualizacji, należy je odpowiednio wydzielić oraz jednoznacznie przyporządkować do zmiennych oraz stempli czasowych.

Posiadany plik zawiera długą sekwencję obiektów JSON takich, jak powyższy. Jednak narzędzie BI-AnalityX jest wyposażony w funkcję „PARSE JSON” pozwalającą na zapis każdej z wartości do osobnego rekordu. Najprościej mówiąc, opcja ta rozdziela wartości z wektora w jednej kolumnie do kilku wierszy.

Pierwszym krokiem w konfiguracji BI-AnalytiX jest więc odpowiednie wstępne przetworzenie danych z pliku. W celu lepszego zrozumienia przedstawianych tu treści, zamieszczono krótki film pokazowy.

Przetwarzanie Danych przez BI-AnalytiX

Kolejny etap to skonfigurowanie odpowiedniego modelu danych. W tym przypadku model danych jest dość trywialny. Składa się on tylko z jednej tabeli uzyskanej za pomocą jednego obiektu typu DataFlow (dodanego wyżej). Nie ma więc potrzeby nadawania tu żadnych relacji pomiędzy tabelami.

Jednak działania te są nieodzowne, gdyż do obiektu DataModel można przyporządkować odpowiedni trigger wymuszający odświeżanie gromadzonych informacji. To z kolei zapewnia, że za każdym razem nastąpi import jedynie najświeższych danych do tabel SQL. Trigger używany w tym miejscu to ten sam, co wykorzystany przy WebServices.

GridWorX – współpraca systemu SCADA i SQL Server

Przed przystąpieniem do budowania właściwego algorytmu, który pozwoli na import danych JSON do baz SQL, należy jeszcze dodać połączenie z docelową bazą danych oraz zdefiniować odpowiedniej operacje.

W tym przypadku utworzono bazę o nazwie JSON_DATA wraz z tabelą dbo.DATA. Baza ta znajduje się za tym samym serwerze, w którym zainstalowane są konfiguracje GENESIS64. Nie jest to jednak wymóg.

Aby poprawnie zdefiniować połączenie oraz operacje na danej bazie danych oraz tabeli należy wykonać poniższe czynności.

  1. Dodać połączenie z docelową bazą danych.
  2. Pod utworzonym połączeniem dodać element typu Data Manipulator. Pozwala on zdefiniować operacje INSERT, UDATE lub DELETE na wybranej tabeli, przy pomocy dowolnej liczby parametrów. Następnie obiekt typu Data Manipulator może być wywołany przez inne narzędzia składowe GENESIS, po inicjalizacji parametrów.

Więcej informacji na temat narzędzia GridWorX znajduje się pod poniższym linkiem.

Poniższe zdjęcie przedstawia proces dodawania nowego połączenia z bazą danych.

Import JSON do SQL - połączenie z bazą danych
Import JSON do SQL – połączenie z bazą danych

Manipulator użyty do omawianego procesu musi realizować kwerendę typu INSERT. Jej składnia jest następująca.

INSERT INTO dbo.DATA (PE, TIM, TAG, RECORD) VALUES (@PE, @TIM, @TAG, @RECORD)

Można zauważyć, że odwołuje się ona do kolumny „TAG” (nazwy zmiennej). Jednak w pliku JSON taka informacja nie była przesyłana. Nie jest to jednak problem – podczas budowy algorytmu będzie możliwy dynamiczny zapis do tej kolumny. Będzie się on odbywał w zależności od numeru aktualnie przetwarzanego rekordu.

JSON to SQL Data Manipulator

BridgeWorX – automatyczny import danych JSON do tabel SQL

Wstęp do BridgeWorX

Mając przygotowane wszystkie narzędzia pomocnicze wczytujące i przetwarzające dane oraz współpracujące z bazami SQL, można przystąpić do budowy właściwego algorytmu.

W celu realizacji tego zadania wykorzystane zostanie narzędzie BridgeWorX. Jest to moduł pozwalający na tworzenie schematów blokowych, które składają się z różnych bloków funkcyjnych. Łącząc je w określony sposób, definiuje się kierunek wykonywania poszczególnych działań.

Wyżej wspomniany schemat zapisany jest jako szablon. To w ustawieniach szablonu dostosowuje się wygląd schematu oraz opcje poszczególnych bloków. Następnie konfigurowana jest właściwa transakcja (instancja narzędzia BridgeWorX wykonująca dany algorytm). To do niej przyporządkowuje się określony szablon, oraz (opcjonalnie) trigger, wyzwalający jej działanie.

Podstawy konfiguracji BrodgeWorX omówiono w artykule, znajdującym się pod poniższym linkiem.

Schemat algorytmu importu danych do baz SQL

Szablon zbudowany na potrzeby projektu prezentuje się następująco.

Schemat algorytmu importu danych JSON do baz SQL
Schemat algorytmu importu danych JSON do baz SQL

Dodatkowo, przy tworzeniu schematu wykorzystano możliwość utworzenia zmiennych globalnych w BridgeWorX. Dodano jedną 3 – elementową tablicę, której każdy wiersz reprezentuje inny licznik. Pierwszy wiersz odpowiada zmiennej przechowującej limit wierszy do zapisania, drugi – przechowującej aktualny numer wiersza, trzeci – aktualny numer zmiennej.

Schemat BridgeWorX - zmienne globalne
Schemat BridgeWorX – zmienne globalne

Opisy poszczególnych elementów algorytmu

Element numer 1 symbolizuje początek schematu. Jest on obowiązkowy.

Obiekt o nazwie „SetRowCounters” to blok typu „RealTimeOutput”. Pozwala on na zapis określonych wartości do zmiennych w czasie rzeczywistym. W tym przypadku wykorzystano go do wyzerowania licznika aktualnego wiersza i aktualnej zmiennej. Konfiguracje jego opcji przedstawia poniższe zdjęcie.


Element „ReadJSONData” to blok typu „Data Set Reader”. Odpowiada on za wczytanie zestawu danych, najczęściej zgrupowanego w tabele, z podanego źródła. W celu skonfigurowania go w odpowiedni sposób, należy jako źródło wskazać odpowiednią tabelę z wcześniej utworzonego modelu AnalytiX-BI.

Obiekt „SetRowLimit” to element typu „Real Time Output”. Zapisuje on do pierwszego z liczników wartość będącą ilością wierszy wczytanych za pomocą wcześniejszego bloku. W związku z tym możliwe jest ustawienie odpowiedniej pętli wykonującej się dokładnie tyle razy, ile rekordów wczytano z pliku JSON.

Jego okno konfiguracyjne jest takie samo, jak to dla obiektu „SetRowCounters”. Odwołanie do odpowiedniej zmiennej oraz wyrażenie do niej zapisywane są następujące.

Odwołanie: bwx:global@RowCounters.Row[0].Col[0]

Wyrażenie: x={{PrevActivityOutput:RowCount}} . Jest to jedna z domyślnych funkcji BridgeWorX. Zwraca ona liczbę wierszy wyjścia poprzedniego bloku.

Obiekt „Check Row Limit Condition”ma za zadanie sprawdzać, czy należy już przerwać działanie algorytmu. W związku z tym, warunek logiczny do niego przypisany ma postać taką jak poniżej.

{{bwx:global@RowCounters.Row[1].Col[0]}} < {{bwx:global@RowCounters.Row[0].Col[0]}}

Jest to porównanie dwóch zmiennych globalnych BridgeWorX. Wyrażenie to jest prawdziwe, jeśli numer aktualnie przetwarzanego rekordu jest mniejszy od liczby wszystkich wierszy przeznaczonych do importu.

Blok funkcyjny zapisu danych do tabel SQL

Element „Write To SQL Database” to blok typu „Data Manipulator”. W celu dokonania jego prawidłowej konfiguracji, należy jako źródło danych wskazać utworzony wcześniej Manipulator wpisujący dane do właściwej tabeli. Następnie w sekcji „Input Parameters” ukażą się automatycznie wykryte parametry danego Manipulatora. Aby je edytować należy wybrać opcję „Configure”.

Po wybraniu w/w opcji możliwa jest zmiana typu, jak i również wartości parametrów. Co więcej, wartości te mogą być przedstawione jako dynamiczne równania.

Należy tu przypomnieć, jak dostarczane dane zostały pogrupowane w tabeli. Pierwsza kolumna Modelu Danych z BI- AnalytiX zawiera wartość PE, druga – TIM, a trzecia – RECORD. Należy więc to uwzględnić przy określaniu wyrażeń dla parametrów. I tak na przykład dla parametru „PE” prezentuje się ono następująco:

x=getoutputcell(„ReadJSONData”, {{bwx:global@RowCounters.Row[1].Col[0]}}, „PE”)

Funkcja getoutputcell() służy wydobyciu wartości z danej komórki tabeli, przy dynamicznie zmieniających się numerach kolumn czy wierszy. Przyjmuje ona nazwę bloku funkcyjnego, którego wyjście ma zbadać, numer wiersza tabeli – tu zmienną globalną (licznik wierszy) oraz numer kolumny.

Dla parametrów „TIM” oraz „RECORD” wyrażenie wygląda podobnie, gdyż zmienia się tylko numer kolumny na odpowiednio: 1 lub 2.

Parametr „TAG” przyjmuje wartość będącą wynikiem działania:

x=”VARIABLE_”+tostring({{bwx:global@RowCounters.Row[2].Col[0]}}) .

Łatwo więc wywnioskować, że zmienne będą posiadać nazwy ze wspólnym przedrostkiem i zmieniającym się numerem na końcu.

Pozostałe bloki funkcyjne

Elementy „IncrementActualRow” oraz „ChangeVariable” to znów bloki typu „Real Time Output”. Pierwszy służy do zwiększania o 1 aktualnego numeru rekordu z Modelu Danych pobranego z BI-AnalytiX, drugi natomiast oblicza odpowiedni numer zmiennej dodawany do jej nazwy. Naturalnie ich okna konfiguracyjne wyglądają tak samo, jak okno obiektu „SetRowCounters”.

Odwołanie do zmiennej dla bloku „IncrementActualRow” jest następujące.

bwx:global@RowCounters.Row[1].Col[0]

Natomiast wyrażenie zapisywane do zmiennej zamieszczone jest poniżej.

x={{bwx:global@RowCounters.Row[1].Col[0]}}+1

Adres zmiennej docelowej w konfiguracji bloku „ChangeVariable” pokazany jest niżej.

bwx:global@RowCounters.Row[2].Col[0]

Zapisywana jest do niej wartość obliczana według poniższego wzoru.

x={{bwx:global@RowCounters.Row[1].Col[0]}}%32+1

Jest to dzielenie modulo 32. Liczba 32 wynika z tego, że w posiadanym pliku JSON do jednego stempla czasowego przyporządkowane są 32 pojedyncze wartości liczbowe. Aby dane z tabeli SQL dało się przedstawić na wykresie, należy zapewnić, że powyższa zmienna przyjmować będzie 32 wartości. Pozwoli to na zapisanie do kolumny „TAG” w tabeli SQL 32 różnych nazw zmiennych.

Element z numerem 9 to znak końca algorytmu. Jest on osiągany, gdy warunek sprawdzany przez obiekt „CheckRowLimitCondition” jest nieprawdziwy. Dotyczy to sytuacji, kiedy numer aktualnego rekordu poddawanego zapisowi do bazy SQL zrówna się z ogólną liczbą rekordów.

Ustawienie transakcji w BridgeWorX

Dostępne opcje konfiguracyjne transakcji w BridgeWorX omówiono w artykule, do którego link podano na początku sekcji. Nie będą tu one więc przytaczane. Nakreślona zostanie natomiast potrzeba wyboru odpowiedniego szablonu oraz ustawienia pożądanego trigger’a w zakładce „Transaction Execution” widocznej na zdjęciu poniżej.

Ustawienia Transakcji BridgeWorX

File Trigger – automatyczne wykonywanie algorytmu

Ostatnim elementem którego wymaga automatyczny import danych JSON do tabel SQL jest konfiguracja odpowiedniego wyzwalacza. Trigger użyty w tym przypadku reaguje na zmiany dotyczące plików i/lub podfolderów w określonej lokalizacji.

Jego konfiguracja przebiega w module Workbench, a dokładną lokalizację obrazuje poniższe zdjęcie. Ponadto pokazano tam najważniejsze opcje dotyczące tego narzędzia.

Użytkownik powinien podać lokalizację poddaną monitorowaniu. Opcjonalnie można ustawić również filtr dla tego narzędzia. Wpisując w zaznaczone poniżej pole np. nazwę pliku JSON, ogranicza się czynniki wyzwalające do tych dotyczących tylko tego pliku.

Na zakończenie konfiguracji należy określić warunki pobudzenia triggera. Na potrzeby tego projektu powinny to być „On File or Subfolder -” :

  • „- Create”,
  • „- Size Change”,
  • „- Last Write Time Change”
Ustawienia Triggera

Podsumowanie i prezentacja importu do tabel SQL

Na zakończenie tego obszernego wpisu warto przypomnieć założenia projektu oraz co dokładnie zaprojektowano na jego potrzeby.

Po pierwsze, prezentowane rozwiązanie pozwala na import danych w formacie JSON (pobieranych z pliku) do tabeli w bazie SQL. Zewnętrze urządzenie wysyła ten plik do określonej lokalizacji. Plik musi mieć niezmienną nazwę oraz być podmieniany w docelowym folderze.

Po drugie, skonfigurowano łącznie 5 komponentów systemu SCADA od ICONICS. Pierwszym z nich był WebService pozwalający na pobranie danych z pliku JSON za pomocą metody HTTP. Kolejne to BI-AnalytiX , które grupuje wydobywa wartości z obiektów JSON i grupuje dane w tabele. Trzecie to GridWorX pozwalający na zarządzanie tabelami SQL z poziomu systemu SCADA. Czwartym komponentem jest BridgeWorX – to w nim nastąpiła budowa kompletnego algorytmu. Piąte narzędzie to File Trigger wyzwalający potrzebne działania w systemie.

Na pierwszy rzut oka powyższe rozwiązanie może wydawać się skomplikowane i nieefektywne. Należy jednak mieć na uwadze, że syztem SCADA GENESIS64 to bardzo wszechstronne narzędzie. Za pomocą tych samych komponentów można by zaprojektować procedurę archiwizacji zmiennych czasu rzeczywistego, przesyłanych w innym formacie. Większa komplikacja rozwiązania jest ceną za jego wszechstronność.

Efekty uzyskane podczas wykonywania kroków przedstawionych w tym wpisie obrazuje poniższy film.

Import Danych JSON do tabeli SQL oraz ich wizualizacja

Elmark Automatyka udostępnia wersję demo oprogramowania GENESIS64 w celu osobistego przetestowania funkcjonalności pakietu. Skontaktuj się z nami na ICONICS@elmark.com.pl w celu otrzymania wersji testowej lub oferty handlowej.