HdInsight.Contents

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function HdInsight.Contents

What is the HdInsight.Contents Function?

The HdInsight.Contents function is a Power Query function that allows users to extract data from Azure HDInsight clusters. It takes two parameters – the URL of the HDInsight cluster and the query to execute. The function then returns a table of data that can be further transformed or loaded into other tools.

The M Code Behind the HdInsight.Contents Function

To understand the M code behind the HdInsight.Contents function, we need to understand how it works. When the function is called, it sends an HTTP request to the HDInsight cluster with the specified URL and query. The response from the cluster is in JSON format, which is then parsed by Power Query to create a table of data.

The M code for the HdInsight.Contents function is as follows:

let

Source = (url as text, query as text) =>

let

options = [

RelativePath = “templeton/v1/hive”,

Headers = [#”Content-Type”=”application/json”]

],

body = “{“”execute””:””” & query & “””}”,

result = Json.Document(Web.Contents(url, options, [Content=Text.ToBinary(body)]))

in

result

in

Source

This code defines a function called "Source" that takes two parameters - the URL and query. It then sets some options for the HTTP request, including the relative path to the Hive endpoint and the content type of the request. The function then creates a JSON body with the specified query and sends an HTTP request to the cluster with the specified URL and options. The response from the cluster is then parsed as a JSON document and returned as a table of data.

Examples of Using the HdInsight.Contents Function

To use the HdInsight.Contents function, we need to provide it with a URL and query. Here are some examples of how to use the function:

Example 1: Extracting Data from a Hive Table

Suppose we have an HDInsight cluster with a Hive table called "mytable". We can extract the data from this table using the following query:

let

Source = HdInsight.Contents(“https://mycluster.azurehdinsight.net”, “SELECT FROM mytable”)

in

Source

This code will return a table of data with all the columns and rows from the "mytable" table.

Example 2: Filtering Data from a Hive Table

Suppose we only want to extract data from the "mytable" table where the "status" column equals "complete". We can modify our query as follows:

let

Source = HdInsight.Contents(“https://mycluster.azurehdinsight.net”, “SELECT FROM mytable WHERE status = ‘complete'”)

in

Source

This code will return a table of data with all the columns and rows from the "mytable" table where the "status" column equals "complete".

Example 3: Joining Data from Multiple Hive Tables

Suppose we have two Hive tables - "table1" and "table2" - and we want to join them on a common column called "id". We can use the following query:

let

Source1 = HdInsight.Contents(“https://mycluster.azurehdinsight.net”, “SELECT FROM table1”),

Source2 = HdInsight.Contents(“https://mycluster.azurehdinsight.net”, “SELECT FROM table2”),

Joined = Table.Join(Source1, “id”, Source2, “id”)

in

Joined

This code will extract data from both "table1" and "table2" and join them on the "id" column. The resulting table will contain all columns from both tables.

The HdInsight.Contents function is a powerful tool for extracting data from Azure HDInsight clusters. By understanding the M code behind this function, we can better leverage its capabilities and extract the data we need for our analysis. Whether we are filtering data, joining tables, or extracting data from a single table, the HdInsight.Contents function is a flexible and efficient way to access HDInsight data in Power Query.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)