Jun 20, 2018 To connect to Databases such as Adaptive Server Enterprise(ASE) an ODBC driver is needed. By default the HANA ODBC Driver is setup, but others are not. UnixODBC is a 3rd party tool to house ODBC drivers from different companies. This product uses 2 different configuration files to store information regarding ODBC drivers.
##SMART DATA ACCESS IN SHINE
1. Overview
#####1.1 What is Smart Data Access?Smart Data Access is a data virtualization feature in SAP HANA. This feature was introduced with SP6 in SAP HANA and allows customers to access data virtually from remote sources (heterogeneous sources) such as Hadoop, Oracle, Teradata, SQL Server and SAP databases and combines it with data that resides in an SAP HANA database.
#####1.2. How does the Smart Data Access function?
- Smart Data Access allows remote access to data stored in other data sources such as Hadoop, Sybase ASE, Teradata, another SAP HANA system etc. without having to move or replicate the data into the SAP HANA system
- Data required from the other remote sources will be available in the virtual tables. The virtual tables points to the remote tables in the other data sources.
- The customers then can send SQL queries in SAP HANA which will operate on the virtual tables. The HANA query processor optimizes these queries, and executes the relevant part of the query in the target database, returns the results of the query to HANA, and completes the operation.
###2. Smart Data Access Scenario
#####2.1. Business Scenario – Product ReviewsAs a retailer of an online store, I want to capture the reviews for the products that are sold online and would like to do the analysis on the reviews provided by the customers.Further Analysis can be done to identify products which are:
• Top rated
• Lowest rated
• Comparisons between sales and rating
• Etc.
• Top rated
• Lowest rated
• Comparisons between sales and rating
• Etc.
#####2.2. Technical Scenario
The product reviews data contain both high and low value data. Since we do not want to over utilize the resources of the SAP HANA system, hence all customer reviews data will be stored stored inside a remote data source (such as Hadoop)
• Primary Data Source: SAP HANA (will contain the product details)
• Remote Data Source: Hadoop (will contain the reviews data)
• Remote Data Source: Hadoop (will contain the reviews data)
###3. Pre-requisites and Configurations
#####3.1. Pre-requisites
The pre-requisites for the smart data access scenario to work are as follows:
1. Unix ODBC driverInstall the Unix ODBC driver in your HANA system. The Unix ODBC driver is needed so that the SAP HANA system can talk to the Hadoop server in order to read the data from the Hadoop system into the HANA system.
For more details on how to do you can follow this video SDA: Configuring ODBC drivers
2. Hive ODBC driver
Install the Hive driver on the machine with the HANA instance.Note: You can install the Hortonworks Hive driver. If the installation runs successful, you should be able to see it in /usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so
For more details you can follow Step 6.3 Install Hortonworks Hive ODBC Driver on SAP HANA AWS Instance in htis pdf.
#####3.2. Configurations
3.2.1. Configuring the DSN:
- Logon to the HANA machine using your HANA admin/super user.
- Copy the /usr/lib/hive/lib/native/hiveodbc/Setup/hortonworks.hiveodbc.ini to your home directory as .hortonworks.hiveodbc.ini
- Edit the .hortonworks.hiveodbc.ini file as follows:
- Add Hive DSN entry to .odbc.ini file in your home directory
- Set environment variables – these exports can be added to $HOME/.customer.sh to avoid having to set them every time.
- Verify that you can connect to Hive through the ODBC Driver
a. Connect to hive using isql
$>isql –v hive1
Note: You should now be able to query hive now. If this does not connect, try logout and login again.
You can follow the video SDA: Configuring ODBC drivers which has the steps.
#####3.2.3. Create table and load data into the table (in the Hadoop system)
1. Download the executable Jar File
Click on this link to download the JAR file utils.uploader-0.0.1-SNAPSHOT-jar-with-dependencies.jar
Click on this link to download the JAR file utils.uploader-0.0.1-SNAPSHOT-jar-with-dependencies.jar
and download sample csv file which has sample Data for the table.Product_Reviews.csv
2. Execute the File
- Double click on the File
- Enter your Hadoop details and Browse and give your CSV which was downloaded
![Driver Driver](/uploads/1/2/6/2/126277982/418834234.png)
- Press Submit
If everything is fine then you would get
#####3.2.3. Import SHINE DU
1.Download The SAP HANA Interactive Education (SHINE) delivery unit (DU) from HCO_DEMOCONTENT.tgz2.Follow Steps in Shine Read Me
#####3.2.4. Create Remote Data Source
- Logon to the HANA system through the HANA studio
- Open the SQL console for the system.
- To create the remote data source run the below SQL command:
CREATE remote source HIVE1
ADAPTER 'hiveodbc'
CONFIGURATION 'DSN=hive1'
WITH CREDENTIAL TYPE 'PASSWORD'
USING 'user=hive;password=hive'; - Now expand the Provisioning folder to see the newly created Remote Source. Expand the Remote Sources node tree structure: *HIVE1HIVEdefault* and check your HIVE table product_reviews
5.Right click on the Hive Table: product_reviews and select: Add as Virtual Table
6.In the Create Virtual Table Pop up window, the Table Name will be displayed as
HIVE1_product_reviews
. Select the Schema as the SAP_HANA_DEMO
.7.Click on the Create button
8.You will get a confirmation on the action done above. Click on the Ok button
8.You will get a confirmation on the action done above. Click on the Ok button
9.Now you can check the newly created Virtual table under
Catalog > Schema SAP_HANA_DEMO > Tables > HIVE1_product_reviews
10.You can view the data in the virtual table as follows:
###3.3. Import and activate the SDA(Smart Data Access) DU
You can download & Import the SDA DU to your HANA System from the location below: HCO DEMOCONTENT SDA
The sequence of activation of the artifacts should be as follows:
- data folder
- procedures folder
- models folder
- services folder
- ui folder
- roles Folder
###3.4 Assign Permission to your User
sap.hana.democontent.epm.sda.roles::sda
to your user.###3.5. Run the Application
You can access the application
https://<host_name>:<port>/sap/hana/democontent/epm/sda/
Here in this scenario, the data which is pertaining to the product comes from the HANA tables and the details on the reviews of the various products come from the virtual table.The user here can now check the details of the various products and provide the review for the product by clicking on the “Add Review” button. This new review provided gets saved in a HANA table. Since Hadoop system only allow mass inserts of data, hence periodically, the data pertaining to the new reviews) provided (from the HANA system) can be pushed to the Hadoop system (using a batch file for mass inserts).Note: Pushing of the new reviews data from the HANA table to the Hadoop table is currently out of scope for this scenario.
###References