Składanie zapytań w kontekście zapytań natywnych

W Power Query można zdefiniować zapytanie natywne i uruchomić je na źródle 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ń przy użyciu funkcji Value.NativeQuery względem źródła danych, aby zachować mechanizm składania zapytań aktywnym w kolejnych krokach zapytania.

Uwaga / Notatka

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:

• Amazon Redshift
• Dataverse(w przypadku korzystania z ulepszonych obliczeń)
• Google BigQuery
• PostgreSQL
• SAP HANA
• Snowflake
• SQL Server

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

Uwaga / Notatka

Aby zaprezentować ten proces, w tym artykule użyto łącznika programu SQL Server i przykładowej bazy danych AdventureWorks2019. Doświadczenie może się różnić w zależności od łącznika, ale ten artykuł pokazuje podstawy, jak włączyć możliwości zawijania 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.

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 warstwą 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 pokazującego pełny widok bazy danych, który jest celem potrzebnym do uruchomienia zapytania natywnego.

Gdy zapytanie zostanie wyświetlone w edytorze Power Query, w okienku Zastosowane kroki powinien być wyświetlony tylko krok Source. Ten krok zawiera tabelę ze wszystkimi dostępnymi obiektami w bazie danych, podobnie jak w przypadku wyświetlania ich w oknie Nawigator.

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.

Uwaga / Notatka

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.

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 zapytania w sekcji Zastosowane Kroki pokazuje krok jako złożony. W tym przypadku można filtrować kolumnę DepartmentID, aby mieć 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.

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 można zobaczyć węzeł o nazwie Value.NativeQuery, który posiada hiperlink 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.

Wskazówka

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.