HADOOP, HIVE, ODBC, SSIS, SQL Azure, SQL Azure Reporting

 

In a previous post, I talked about analyzing 1 TB of IIS logs with JavaScript thru Hadoop Map/Reduce. In this post, let’s see how to copy and use the result of these jobs to SQL Azure.	Dans un billet précédent, il a été question de l'analyse d'1 To de logs IIS avec JavaScript dans Hadoop Map/Reduce. Dans ce billet, regardons comment copier et exploiter le résultat dans SQL Azure.
First of all, from 1 TB of IIS logs, we had a result of less than 100 GB for the headers and details, so having this data in SQL Azure (or SQL Server) will be more efficient than keeping it in Hadoop: Hadoop Map/Reduce is a world of batches that can handle bigger and bigger amounts of data linearly while SQL Azure is interactive (but requires SQL Azure federations to scale linearly).	Tout d’abord, à partir du To de logs initial, le résultat a été de moins de 100 Go pour les en-têtes et les détails, et donc avoir ces données résultats dans SQL Azure (ou SQL Server) sera plus efficace que de les laisser dans Hadoop: Hadoop Map/Reduce correspond à un monde de traitements par lots qui peut gérer des données toujours plus grosses avec des performances linéaires alors que SQL Azure est interactif (mais nécessite les fédérations SQL Azure pour une montée en charge linéaire).
The steps are the following: - create a SQL Azure database - create HIVE metadata - open ODBC port - install ODBC driver - create SSIS package - Run SSIS package - Create and run a SQL Azure Report on top of database	Les étapes sont les suivantes: - créer une base SQL Azure - créer des métadonnées HIVE - ouvrir le port ODBC - installer le pilote ODBC - créer le package SSIS - exécuter le package SSIS - créer et exécuter un rapport SQL Azure Reporting au dessus de cette base

 

Create a SQL Azure database	créer une base SQL Azure
A SQL Azure database can be created as explained here.	On peut créer une base SQL Azure comme expliqué ici.
Note that if one already has a SQL Azure database, he can reuse it but he might have to change its size. For instance to let a SQL Azure database grow from 1 GB to 150 GB, the following statement should be issued against the master database in the same SQL Azure server:	A noter que si on dispose déjà d’une base SQL Azure, il est possible de la réutiliser après éventuellement avoir changé sa taille. Par exemple, pour faire en sorte qu’une base SQL Azure puisse grossir non plus jusqu’à 1 Go mais jusqu’à 150 Go, la requête suivante peut être exécutée sur la base master du serveur SQL Azure:

ALTER DATABASE demo MODIFY (MAXSIZE = 150 GB, EDITION='business')

 

Create HIVE metadata	créer des métadonnées HIVE
HIVE can store its data in HDFS with a variety of format. It can also reference existing data without directly managing it. This king of storage is called external tables. In our case, we want to expose the iislogsH and iislogsD HDFS folders as HIVE tables. Here how the folders look in HDFS:	HIVE peut stocker ses données dans HDFS sous diverses formes. Il est aussi capable de référencer des données existantes sans les gérer directement. Ce type de stockage correspond aux tables externes. Dans notre cas, on veut exposer les dossiers HDFS iislogsH et iislogsD sous forme de tables HIVE. Voici à quoi ressemblent les dossiers HDFS en question:

(…)

 

(…)

js> #tail iislogsH/part-m-00998
731g2x183 2012-01-25 19:01:05 2012-01-25 19:22:11 16
872410056 a34175aac900ed11ea95205e6c600d461adafbac test1833g4x659 2012-01-27 13:23:31 2012-01-27 13:39:28 4
872410676 a34182f009cea37ad87eb07e5024b825b0057fff test1651hGx209 2012-01-28 18:05:45 2012-01-28 18:41:37 7
872411200 a341afd002814dcf39f9837558ada25410a96669 test2250g4x61 2012-01-27 01:04:45 2012-01-27 01:23:19 5
872412076 a341b71e15a5ba5776ed77a924ddaf49d89bab54 test4458gAx34 2012-02-02 19:00:07 2012-02-02 19:09:12 10
872412288 a341c30edf95dfd0c43a591046eea9eebf35106e test2486g4x352 2012-01-27 17:05:52 2012-01-27 17:27:54 2
872412715 a341cb284b21e10e60d895b360de9b570bee9444 test3126g2x205 2012-01-25 13:07:47 2012-01-25 13:49:20 4
872413239 a341cc54e95d23240c4dfc4455f6c8af2a621008 test0765g7x99 2012-01-30 00:08:04 2012-01-30 00:43:17 5
872414168 a341d37eb48ae06c66169076f48860b1a3628d49 test0885g4x227 2012-01-27 18:02:40 2012-01-27 18:11:12 11
872414800 a341d8762f0d7e1e70ced16ce10786ea50ef3ca7 test2471hHx165 2012-01-29 20:00:58 2012-01-29 20:40:24 6

js> #tail iislogsD/part-m-00998
9 a341d8762f0d7e1e70ced16ce10786ea50ef3ca7 20:40:24 /
872414323 a341d8762f0d7e1e70ced16ce10786ea50ef3ca7 20:07:49 /cuisine-francaise/huitres
872414402 a341d8762f0d7e1e70ced16ce10786ea50ef3ca7 20:00:58 /cuisine-francaise/gateau-au-chocolat-et-aux-framboises
872414510 a341d8762f0d7e1e70ced16ce10786ea50ef3ca7 20:29:52 /cuisine-francaise/gateau-au-chocolat-et-aux-framboises
872414618 a341d8762f0d7e1e70ced16ce10786ea50ef3ca7 20:39:51 /Users/Account/LogOff
872414692 a341d8762f0d7e1e70ced16ce10786ea50ef3ca7 20:09:52 /cuisine-francaise/gateau-au-chocolat-et-aux-framboises
872414900 a341db58e7be1b37828ed5591ba17c251c96a16a 03:00:23 /Modules/Orchard.Localization/Styles/orchard-localization-base.css
872415019 a341db58e7be1b37828ed5591ba17c251c96a16a 03:00:57 /Users/Account/LogOff
872415093 a341db58e7be1b37828ed5591ba17c251c96a16a 03:00:12 /Themes/Classic/Styles/Site.css
872415177 a341db58e7be1b37828ed5591ba17c251c96a16a 03:00:56 /
872415231 a341db58e7be1b37828ed5591ba17c251c96a16a 03:00:44 /Core/Shapes/scripts/html5.js

The following statements will expose the folders as HIVE external tables:

Les requêtes suivantes vont exposer les dossiers sous forme de tables externes:

CREATE EXTERNAL TABLE iisLogsHeader (rowID STRING, sessionID STRING, username STRING, startDateTime STRING, endDateTime STRING, nbUrls INT)
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/user/cornac/iislogsH'

 

CREATE EXTERNAL TABLE iisLogsDetail (rowID STRING, sessionID STRING, HitTime STRING, Url STRING)
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/user/cornac/iislogsD'

These statements can be entered in the HadoopOnAzure portal

Ces requêtes peuvent être entrées dans le portail HadoopOnAzure

 

It is possible to check that data is exposed thru HIVE by issuing this kind of statement. Note that using SELECT * will not generate a map/reduce job because there is no need to change data before returning it.

Il est possible de vérifier que les données sont exposées en exécutant ce type de requête. A noter que l’utilisation de SELECT * ne génère pas de job map/reduce puisqu’aucun traitement préalable des données n’est nécessaire.

SELECT * FROM iislogsheader LIMIT 20

SELECT * FROM iislogsdetail LIMIT 20

 

Open ODBC port	ouvrir le port ODBC
From HadoopOnAzure portal, go to the Open Ports tile and make sure ODBC port 10000 is open	Depuis le portail HadoopOnAzure, aller à la tuile “Open Ports” et s’assurer que le port 10000 ODBC est ouvert

 

Install ODBC driver	installer le pilote ODBC
On the server where SSIS will run, there must be the HIVE ODBC driver installed.	Sur le serveur ou SSIS s’exécutera, il faut avoir le pilote ODBC pour HIVE installé.
It can be installed from the HadoopOnAzure portal, by clicking the “Downloads” tile and choosing the right MSI:	Il peut s’installer depuis le portail HadoopOnAzure, en cliquant sur la tuile “Downloads” et en choisissant le MSI adapté à sa machine:

 

create an SSIS Package	créer le package SSIS
A general guidance on how to create an SSIS package to fill SQL Azure is available here.	Des instructions générales pour créer un package SSIS à destination de SQL Azure est dsiponible là.
The user DSN (ODBC entry) can be created from Windows Control Panel.	Le DSN (entrée ODBC) utilisateur peut être créé depuis le panneau de configuration de Windows.

 

In order to connect to HIVE thru ODBC, one should usein SSIS ODBC driver for ADO.NET

De façon à se connecter à HIVE via ODBC, on passe dans SSIS par le pilote ODBC pour ADO.NET

 

By default, the number of allowed errors is 1. You may want to change this value, by selecting the data flow, then the control flow and changing its MaximumErrorCount property. One could also change timeouts.

Par défaut, le nombre d’erreurs autorisées est 1. Il peut être nécessaire de changer cette valeur, en sélectionnant le “data flow”, puis le “control flow” et en changeant la propriété MaximumErrorCount. On pourra aussi augmenter certains délais d’attente.

 

While creating the package, you will get a chance to have the destination tables created automatically. Here are the resulting tables schemas in SQL Azure (as screenshots). As Hadoop map/reduce may generate rowid duplicates in our case, the constraint on rowid will be removed by droping and recreating the tables with the code provided here (change is underlined). The principle is to leave original rowid on Hadoop cluster and create new ones in SQL.

Lors de la création du package, il sera proposé de créer les tables de destination automatiquement. Voici (sous forme de copies d’écrans) les schémas ainsi générés dans SQL Azure. Comme Hadoop map/reduce peut avoir généré des doublons dans notre cas, les contraintes sur rowid vont être supprimées en supprimant et recréant les tables avec le code fourni ici (le changement est souligné). Le principe est de laisser les rowid sur le cluster Hadoop et d’en regénérer d’autres dans SQL.

DROP TABLE [dbo].[iislogsheader]
GO

CREATE TABLE [dbo].[iislogsheader](
    [rowid] bigint IDENTITY(1,1) NOT NULL,
    [sessionid] [nvarchar](334) NULL,
    [username] [nvarchar](334) NULL,
    [startdatetime] [nvarchar](334) NULL,
    [enddatetime] [nvarchar](334) NULL,
    [nburls] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [rowid] ASC
)
)

 

DROP TABLE [dbo].[iislogsdetail]
GO

CREATE TABLE [dbo].[iislogsdetail](
    [rowid] bigint IDENTITY(1,1) NOT NULL,
    [sessionid] [nvarchar](334) NULL,
    [hittime] [nvarchar](334) NULL,
    [url] [nvarchar](334) NULL,
PRIMARY KEY CLUSTERED
(
    [rowid] ASC
)
)

It will also be necessary to remove the link between source and destination rowid columns as they are independant.

Il sera aussi nécessiare de supprimer les liens entre les colonnes rowid source et destinations, maintenant qu’elles sont indépendantes.

 

Run SSIS Package	exécuter le package SSIS
The SSIS package can be depoyed and run. In this sample, it is run from the debugger (F5 in Visual Studio).	Le package SSIS peut être déployé et exécuté. Dans cet exemple, on le lance depuis le débogueur (F5 dans Visual Studio).

 

Create and run a SQL Azure Report	Créer et exécuter un rapport SQL Azure Reporting au dessus de cette base
A report can be built on top of this SQL Azure database as explained here.	Un rapport peut ensuite être créé au dessus de la base SQL Azure comme expliqué ici.
For instance the report designer looks like this	Par exemple, le report designer peut ressembler à cela

 

and the report looks like this

et le rapport à cela

Related link | Lien connexe: Partager vos données via SQL Azure

 

Benjamin

Comments

• Anonymous
  May 14, 2012
  Is there a similar ODBC driver for HIVE that works out of Azure? I am wodering if you can do the above demo with  a SQL Server and  a Hadoop cluster (Both of them not in Azure).Thanks