Udostępnij za pośrednictwem

Składanie zapytań w zapytaniach natywnych

  • Artykuł

W dodatku Power Query można zdefiniować zapytanie natywne i uruchomić je względem źródła danych. W artykule Import data from a database using native database query (Importowanie danych z bazy danych przy użyciu natywnego zapytania bazy danych) wyjaśniono, jak wykonać ten proces z wieloma źródłami danych. Jednak przy użyciu procesu opisanego w tym artykule zapytanie nie korzysta z możliwości składania zapytań z kolejnych kroków zapytania.

W tym artykule przedstawiono alternatywną metodę tworzenia natywnych zapytań względem źródła danych przy użyciu funkcji Value.NativeQuery i aktywnego mechanizmu składania zapytań w kolejnych krokach zapytania.

Uwaga

Zalecamy przeczytanie dokumentacji dotyczącej składania zapytań i wskaźników składania zapytań, aby lepiej zrozumieć pojęcia używane w tym artykule.

Obsługiwane łączniki danych

Metoda opisana w następnych sekcjach dotyczy następujących łączników danych:

Nawiązywanie połączenia z obiektem docelowym ze źródła danych

Uwaga

Aby zaprezentować ten proces, w tym artykule użyto łącznika programu SQL Server i przykładowej bazy danych AdventureWorks2019. Środowisko może się różnić od łącznika do łącznika, ale w tym artykule przedstawiono podstawowe informacje na temat włączania możliwości składania zapytań w zapytaniach natywnych dla obsługiwanych łączników.

Podczas nawiązywania połączenia ze źródłem danych ważne jest, aby nawiązać połączenie z węzłem lub poziomem, na którym chcesz wykonać zapytanie natywne. Na przykład w tym artykule węzeł ten jest poziomem bazy danych na serwerze.

Zrzut ekranu przedstawiający okno dialogowe ustawień połączenia dla połączenia z bazą danych AdventureWorks2019 w lokalnym wystąpieniu programu SQL Server.

Po zdefiniowaniu ustawień połączenia i podaniu poświadczeń dla połączenia zostanie otwarte okno dialogowe nawigacji dla źródła danych. Okno dialogowe nawigacji zawiera wszystkie dostępne obiekty, z którymi można nawiązać połączenie.

Z tej listy należy wybrać obiekt, w którym jest uruchamiane zapytanie natywne (znane również jako obiekt docelowy). W tym przykładzie obiekt ten jest poziomem bazy danych.

W oknie nawigatora w dodatku Power Query wybierz i przytrzymaj (lub kliknij prawym przyciskiem myszy) węzeł bazy danych w oknie nawigatora i wybierz opcję Przekształć dane . Wybranie tej opcji powoduje utworzenie nowego zapytania ogólnego widoku bazy danych, który jest elementem docelowym, który należy uruchomić zapytanie natywne.

Zrzut ekranu przedstawiający wybieranie danych, w których użytkownik kliknął prawym przyciskiem myszy węzeł bazy danych w nawigatorze z podkreślonym przekształceniem danych.

Gdy zapytanie zostanie wyświetlone w edytorze Power Query, w okienku Zastosowane kroki powinny być wyświetlane tylko kroki źródłowe . Ten krok zawiera tabelę ze wszystkimi dostępnymi obiektami w bazie danych, podobnie jak w przypadku wyświetlania ich w oknie Nawigator.

Zrzut ekranu przedstawiający zapytanie z tylko krokiem źródłowym.

Użyj funkcji Value.NativeQuery

Celem tego procesu jest wykonanie następującego kodu SQL i zastosowanie większej liczby przekształceń za pomocą dodatku Power Query, które można składać z powrotem do źródła.

SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'

Pierwszym krokiem było zdefiniowanie prawidłowego obiektu docelowego, w tym przypadku jest to baza danych, w której jest uruchamiany kod SQL. Gdy krok ma prawidłowy cel, możesz wybrać ten krok — w tym przypadku źródło w zastosowanych krokach — a następnie wybrać przycisk fx na pasku formuły, aby dodać krok niestandardowy. W tym przykładzie zastąp formułę Source następującą formułą:

Value.NativeQuery(Source, "SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'  ", null, [EnableFolding = true])

Najważniejszym składnikiem tej formuły jest użycie opcjonalnego rekordu dla czwartego parametru funkcji, która ma pole rekordu EnableFolding ustawione na wartość true.

Zrzut ekranu przedstawiający nową formułę kroku niestandardowego z użyciem funkcji Value.NativeQuery i jawnego zapytania SQL.

Uwaga

Więcej informacji na temat funkcji Value.NativeQuery można przeczytać w oficjalnym artykule dokumentacji.

Po wprowadzeniu formuły zostanie wyświetlone ostrzeżenie, które wymaga włączenia natywnych zapytań w celu uruchomienia określonego kroku. Wybierz pozycję Kontynuuj, aby ocenić ten krok.

Ta instrukcja SQL zwraca tabelę zawierającą tylko trzy wiersze i dwie kolumny.

Zrzut ekranu przedstawiający wyniki zapytania natywnego ocenianego względem docelowej bazy danych.

Składanie zapytań testowych

Aby przetestować składanie zapytania, możesz spróbować zastosować filtr do dowolnej kolumny i sprawdzić, czy wskaźnik składania zapytań w sekcji zastosowane kroki pokazuje krok złożony. W tym przypadku można filtrować kolumnę DepartmentID, aby mieć wartości, które nie są równe dwóm.

Zrzut ekranu przedstawiający sposób filtrowania kolumny DepartmentID w celu posiadania tylko wartości, które nie są równe dwóm.

Po dodaniu tego filtru możesz sprawdzić, czy wskaźniki składania zapytań nadal pokazują składanie zapytań wykonywane w tym nowym kroku.

Krok filtru pokazany jako złożony z powrotem do źródła danych w sekcji zastosowane kroki.

Aby dokładniej sprawdzić, jakie zapytanie jest wysyłane do źródła danych, możesz wybrać i wstrzymać (lub kliknąć prawym przyciskiem myszy) krok Filtrowane wiersze i wybrać opcję Wyświetl plan zapytania, aby sprawdzić plan zapytania dla tego kroku.

W widoku planu zapytania widać, że węzeł o nazwie Value.NativeQuery z hiperlinkiem Wyświetl szczegóły . Możesz wybrać to hiperłącze, aby wyświetlić dokładne zapytanie wysyłane do bazy danych programu SQL Server.

Zapytanie natywne jest opakowane wokół innej instrukcji SELECT w celu utworzenia podzapytania oryginalnego. Dodatek Power Query najlepiej tworzy najbardziej optymalne zapytanie, biorąc pod uwagę użyte przekształcenia i udostępnione zapytanie natywne.

Zrzut ekranu przedstawiający plan zapytania dla kroku Przefiltrowane wiersze.

Napiwek

W przypadku scenariuszy, w których występują błędy, ponieważ składanie zapytań nie było możliwe, zalecamy wypróbowanie weryfikacji kroków jako podzapytania oryginalnego zapytania natywnego, aby sprawdzić, czy występują jakiekolwiek konflikty składni lub kontekstu.