Since Hive tables are exposed only through ODBC, there is no direct way to connect an Analysis Services database to Hadoop as Analysis Services can only connect to OLE DB compliant data sources. To address this, the only way is to create a Linked Server in a SQL Server instance using Hive ODBC driver and consume it through OLE DB for ODBC. We will reuse the DSN HadoopOnLinux that we created during our earlier chapters to connect to Hive.
To create the Linked Server, perform the following steps:
Connect to the SQL Server instance using SQL Server Management Studio and expand the Server Objects as shown in the following screenshot:
Right-click on Linked Servers and choose New Linked Server. This should bring up the New Linked Server window as shown in the following screenshot:
You have to fill in the details of the Hive Data source that you would like connect to. In this case, I have named the Linked Server as LINKTOHIVE. You have to choose Microsoft OLE...