When working with Power BI, connecting to data sources is a critical step. One data source that is becoming increasingly popular is Hive LLAP. Hive LLAP is a data warehouse technology that allows for querying of large datasets stored in the Hadoop Distributed File System (HDFS). In this article, we will explore how to connect to the Hive LLAP data source using Power Query M Language code from inside Power BI.
Prerequisites
Before we begin, there are a few prerequisites that need to be in place:
– Power BI Desktop must be installed on your machine.
– The Hive LLAP data source must be accessible from your machine.
– The required ODBC driver for Hive LLAP must be installed on your machine.
If any of these prerequisites are not met, please ensure they are in place before proceeding.
Creating the Connection
To create a connection to the Hive LLAP data source, we will first need to open Power BI Desktop. From the Home screen, select the “Get Data” option in the ribbon. In the “Get Data” window, select “ODBC” from the list of available connections.
In the “ODBC” window, select the “Advanced” option. In the “Advanced Options” window, we will need to enter the connection string for the Hive LLAP data source. The connection string will look something like this:
Driver={ODBC Driver for Apache Hive};Host=;Port=;Schema=;UID=;PWD=;
Replace `` with the hostname or IP address of your Hive LLAP server, `` with the port number for the Hive LLAP server (default is usually 10000), `` with the Hive database name, `` with the username for connecting to the Hive LLAP data source, and `` with the corresponding password.
Once the connection string has been entered, select “Connect” to create the connection to the Hive LLAP data source.
Querying the Data
Now that we have created the connection to the Hive LLAP data source, we can begin querying the data. To do this, we will need to add a new query to our Power BI report. From the Home screen, select the “New Source” option in the ribbon. In the “New Source” window, select “Blank Query”.
In the “New Query” window, select the “Advanced Editor” option. In the “Advanced Editor” window, we can begin writing our Power Query M Language code to query the data. Here is an example of Power Query M Language code for connecting to the Hive LLAP data source:
let
Source = Odbc.DataSource(“dsn=“),
Query = “SELECT FROM
“,
Results = Odbc.Query(Source, Query)
in
Results
Replace `` with the name of the ODBC data source name configured on your machine and `
` with the name of the table you wish to query.
Once the Power Query M Language code has been entered, select “Done” to create the query. From here, you can manipulate the data as needed using Power Query transformations.
Conclusion
Connecting to the Hive LLAP data source from inside Power BI is a powerful capability that allows for querying of large datasets stored in the Hadoop Distributed File System. With the Power Query M Language code provided in this article, you can easily create a connection to the Hive LLAP data source and begin querying the data.
Power BI Training Courses by G Com Solutions (0800 998 9248)
Power Query is a powerful tool in Power BI that allows data analysts and business intelligence professionals to connect to various data sources, transform data, and load it into Power BI. The M language is the backbone of Power Query, and it is responsible for data transformation and data loading processes. Power Query supports various data sources, including databases, web services, and data files. In this article, we will discuss how to connect to the Kyligence data source using Power Query M language code from inside Power BI.
Data transformation plays a pivotal role in the world of data analytics, enabling organizations to derive valuable insights from raw data. In the realm of business intelligence, Microsoft Power BI stands out as a powerful tool for data visualization and analysis. At the heart of Power BI’s data transformation capabilities lies Power Query, a robust and versatile tool that empowers users to reshape, cleanse, and enrich their data. In this article, we will delve into the intricacies of the Power Query Query Settings Pane, exploring its various features and functionalities to unleash the true potential of data transformation in Power BI.
In today’s data-driven world, clean and accurate data is crucial for effective analysis and decision-making. However, working with large datasets often comes with its fair share of challenges, including data inconsistencies and formatting issues. Thankfully, Power BI provides powerful tools to tackle data cleanup tasks efficiently. One such tool is the Trim function, which can be a game-changer when it comes to transforming messy data into pristine, ready-to-use information.
Introduction Imagine a world where your business processes run like a well-oiled machine, with data flowing seamlessly between systems, triggering actions without human intervention. Now, consider how much time and money your organization could save if you could automate these processes with tools you already use daily. This post explores how you can leverage the…
Introduction Did you know that companies with effective feedback mechanisms are 21% more likely to experience above-average profitability? Yet, most organizations struggle to translate employee feedback into actionable insights, leading to missed opportunities and declining engagement. This post will explore how you can leverage Power BI to turn raw employee feedback data into informed decisions…
If you are considering Power BI training, one of your first decisions is choosing between sending your staff on a scheduled training course and arranging in-house Power BI training. There are pros and cons to both approaches. Booking a place on a public Power BI training course One of the main benefits of booking a…
