Składanie zapytań w zapytaniach natywnych

W dodatku Power Query możesz 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 skorzysta 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:

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

Połączenie do miejsca docelowego 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 podstawy dotyczące 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 będzie poziomem bazy danych na serwerze.

Po zdefiniowaniu ustawień połączenia i podaniu poświadczeń dla połączenia nastąpi przejście do okna dialogowego nawigacji dla źródła danych. W tym oknie dialogowym zostaną wyświetlone 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 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.

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.

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 zostanie uruchomiony 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 parametru forth funkcji, która ma pole rekordu EnableFolding ustawione na wartość true.

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 będzie wymagać włączenia natywnych zapytań w celu uruchomienia określonego kroku. Aby ocenić ten krok, możesz kliknąć przycisk Kontynuuj.

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 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.

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 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 po lewej stronie ekranu z tekstem hiperłącza, który odczytuje szczegóły widoku. Możesz kliknąć ten tekst hiperłącza, 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 zrobi wszystko, co w jego mocy, aby utworzyć najbardziej optymalne zapytanie, biorąc pod uwagę użyte przekształcenia i udostępnione zapytanie natywne.

Napiwek

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