.. _azurepigrst:

==============
HDInsight, PIG
==============


.. only:: html

    **Links:** :download:`notebook <azure_pig.ipynb>`, :downloadlink:`html <azure_pig2html.html>`, :download:`PDF <azure_pig.pdf>`, :download:`python <azure_pig.py>`, :downloadlink:`slides <azure_pig.slides.html>`, :githublink:`GitHub|_doc/notebooks/2016/centrale/azure_pig.ipynb|*`


Short examples on how to connect to a cluster from a notebook and submit
a job (Azure + PIG).

.. code:: ipython3

    from jyquickhelper import add_notebook_menu
    add_notebook_menu()






.. contents::
    :local:





Download the data
-----------------

.. code:: ipython3

    url = "https://archive.ics.uci.edu/ml/machine-learning-databases/00222/"
    file = "bank.zip"
    import pyensae
    data = pyensae.download_data(file, website=url)

.. code:: ipython3

    import pandas
    df = pandas.read_csv("bank-full.csv", sep=";")

.. code:: ipython3

    df.head()






.. raw:: html

    <div>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>age</th>
          <th>job</th>
          <th>marital</th>
          <th>education</th>
          <th>default</th>
          <th>balance</th>
          <th>housing</th>
          <th>loan</th>
          <th>contact</th>
          <th>day</th>
          <th>month</th>
          <th>duration</th>
          <th>campaign</th>
          <th>pdays</th>
          <th>previous</th>
          <th>poutcome</th>
          <th>y</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>58</td>
          <td>management</td>
          <td>married</td>
          <td>tertiary</td>
          <td>no</td>
          <td>2143</td>
          <td>yes</td>
          <td>no</td>
          <td>unknown</td>
          <td>5</td>
          <td>may</td>
          <td>261</td>
          <td>1</td>
          <td>-1</td>
          <td>0</td>
          <td>unknown</td>
          <td>no</td>
        </tr>
        <tr>
          <th>1</th>
          <td>44</td>
          <td>technician</td>
          <td>single</td>
          <td>secondary</td>
          <td>no</td>
          <td>29</td>
          <td>yes</td>
          <td>no</td>
          <td>unknown</td>
          <td>5</td>
          <td>may</td>
          <td>151</td>
          <td>1</td>
          <td>-1</td>
          <td>0</td>
          <td>unknown</td>
          <td>no</td>
        </tr>
        <tr>
          <th>2</th>
          <td>33</td>
          <td>entrepreneur</td>
          <td>married</td>
          <td>secondary</td>
          <td>no</td>
          <td>2</td>
          <td>yes</td>
          <td>yes</td>
          <td>unknown</td>
          <td>5</td>
          <td>may</td>
          <td>76</td>
          <td>1</td>
          <td>-1</td>
          <td>0</td>
          <td>unknown</td>
          <td>no</td>
        </tr>
        <tr>
          <th>3</th>
          <td>47</td>
          <td>blue-collar</td>
          <td>married</td>
          <td>unknown</td>
          <td>no</td>
          <td>1506</td>
          <td>yes</td>
          <td>no</td>
          <td>unknown</td>
          <td>5</td>
          <td>may</td>
          <td>92</td>
          <td>1</td>
          <td>-1</td>
          <td>0</td>
          <td>unknown</td>
          <td>no</td>
        </tr>
        <tr>
          <th>4</th>
          <td>33</td>
          <td>unknown</td>
          <td>single</td>
          <td>unknown</td>
          <td>no</td>
          <td>1</td>
          <td>no</td>
          <td>no</td>
          <td>unknown</td>
          <td>5</td>
          <td>may</td>
          <td>198</td>
          <td>1</td>
          <td>-1</td>
          <td>0</td>
          <td>unknown</td>
          <td>no</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    df.to_csv("bank_full_tab_no.txt", sep="\t", index=False, header=None)

Connect to the cluster
----------------------

.. code:: ipython3

    import pyensae
    blobstorage = 
    blobpassword = 
    hadoop_server = 
    hadoop_password = 
    username = "centrale"
    client, bs =  %hd_open
    client, bs




.. parsed-literal::
    (<pyensae.remote.azure_connection.AzureClient at 0x1a349a00550>,
     <azure.storage.blob.blockblobservice.BlockBlobService at 0x1a349a314a8>)



Upload the data
---------------

.. code:: ipython3

    %blob_up bank_full_tab_no.txt hdblobstorage/centrale2/bank_full_tab_no.txt




.. parsed-literal::
    'centrale2/bank_full_tab_no.txt'



.. code:: ipython3

    %blob_ls hdblobstorage/centrale2






.. raw:: html

    <div>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>name</th>
          <th>last_modified</th>
          <th>content_type</th>
          <th>content_length</th>
          <th>blob_type</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>centrale2/bank_full_tab_no.txt</td>
          <td>2016-06-16 10:18:58+00:00</td>
          <td>None</td>
          <td>3751188</td>
          <td>BlockBlob</td>
        </tr>
      </tbody>
    </table>
    </div>



Submit a PIG query
------------------

.. code:: ipython3

    mapping = {'int64': 'double', 'float': 'double', 'object': 'chararray'}
    schema = ["%s:%s" % (_[0], mapping.get(str(_[1]), _[1])) for _ in zip(df.columns, df.dtypes)]
    schema = ", ".join(schema)
    schema




.. parsed-literal::
    'age:double, job:chararray, marital:chararray, education:chararray, default:chararray, balance:double, housing:chararray, loan:chararray, contact:chararray, day:double, month:chararray, duration:double, campaign:double, pdays:double, previous:double, poutcome:chararray, y:chararray'



On ajoute l’instruction
`DESCRIBE <http://pig.apache.org/docs/r0.16.0/test.html#describe>`__.

.. code:: ipython3

    %%PIG_azure aggage3.pig
    values = LOAD '$CONTAINER/centrale/bank_full_tab_no.txt' USING PigStorage('\t') AS (age:double, 
                        job:chararray, marital:chararray, education:chararray, 
                       default:chararray, balance:double, housing:chararray, loan:chararray, 
                       contact:chararray, day:double, month:chararray, duration:double, 
                       campaign:double, pdays:double, previous:double, poutcome:chararray, y:chararray);
    DESCRIBE values;
    gr = GROUP values BY loan ;
    DESCRIBE gr;
    agg = FOREACH gr GENERATE group, AVG(age) AS avg_age ;
    DESCRIBE agg;
    STORE agg INTO '$CONTAINER/centrale/bank_full_tab_no_agg.txt' USING PigStorage('\t') ;

.. code:: ipython3

    jid = %hd_pig_submit aggage3.pig

.. code:: ipython3

    jid




.. parsed-literal::
    {'id': 'job_1466069083851_0005'}



.. code:: ipython3

    %hd_queue




.. parsed-literal::
    [{'detail': None, 'id': 'job_1466069083851_0005'},
     {'detail': None, 'id': 'job_1466069083851_0004'},
     {'detail': None, 'id': 'job_1466069083851_0003'},
     {'detail': None, 'id': 'job_1466069083851_0002'},
     {'detail': None, 'id': 'job_1466069083851_0001'}]



.. code:: ipython3

    df = %hd_job_status jid['id']
    df["status"]["state"]




.. parsed-literal::
    'RUNNING'



.. code:: ipython3

    %hd_tail_stderr -n 100 jid['id']






.. raw:: html

    <pre>
    16/06/16 21:05:43 INFO pig.ExecTypeProvider: Trying ExecType : LOCAL
    16/06/16 21:05:43 INFO pig.ExecTypeProvider: Trying ExecType : MAPREDUCE
    16/06/16 21:05:43 INFO pig.ExecTypeProvider: Picked MAPREDUCE as the ExecType
    2016-06-16 21:05:43,576 [main] INFO  org.apache.pig.Main - Apache Pig version 0.15.0.2.3.3.1-21 (r: unknown) compiled May 04 2016, 20:06:44
    2016-06-16 21:05:43,576 [main] INFO  org.apache.pig.Main - Logging error messages to: C:\apps\dist\hadoop-2.7.1.2.3.3.1-21\logs\pig_1466111143557.log
    2016-06-16 21:05:45,088 [main] INFO  org.apache.pig.impl.util.Utils - Default bootup file D:\Users\hdp/.pigbootup not found
    2016-06-16 21:05:45,498 [main] INFO  org.apache.hadoop.conf.Configuration.deprecation - mapred.job.tracker is deprecated. Instead, use mapreduce.jobtracker.address
    2016-06-16 21:05:45,498 [main] INFO  org.apache.hadoop.conf.Configuration.deprecation - fs.default.name is deprecated. Instead, use fs.defaultFS
    2016-06-16 21:05:45,498 [main] INFO  org.apache.pig.backend.hadoop.executionengine.HExecutionEngine - Connecting to hadoop file system at: wasb://clusterensaeazure1-3@hdblobstorage.blob.core.windows.net
    2016-06-16 21:05:47,452 [main] INFO  org.apache.hadoop.conf.Configuration.deprecation - fs.default.name is deprecated. Instead, use fs.defaultFS
    2016-06-16 21:05:49,057 [main] <b><font color="#DD0000">ERROR</font></b> org.apache.pig.tools.grunt.Grunt - <b><font color="#DD0000">ERROR</font></b> 1025: 
    <file aggage3.pig, line 10, column 37> Invalid field projection. Projected field [age] does not exist in schema: group:chararray,values:bag{:tuple(age:double,job:chararray,marital:chararray,education:chararray,default:chararray,balance:double,housing:chararray,loan:chararray,contact:chararray,day:double,month:chararray,duration:double,campaign:double,pdays:double,previous:double,poutcome:chararray,y:chararray)}.
    2016-06-16 21:05:49,057 [main] <b><font color="#DD0000">ERROR</font></b> org.apache.pig.tools.grunt.Grunt - org.apache.pig.impl.logicalLayer.FrontendException: <b><font color="#DD0000">ERROR</font></b> 1001: Unable to describe schema for alias agg
    	at org.apache.pig.PigServer.dumpSchema(PigServer.java:823)
    	at org.apache.pig.tools.grunt.GruntParser.processDescribe(GruntParser.java:321)
    	at org.apache.pig.tools.pigscript.parser.PigScriptParser.parse(PigScriptParser.java:416)
    	at org.apache.pig.tools.grunt.GruntParser.parseStopOnError(GruntParser.java:230)
    	at org.apache.pig.tools.grunt.GruntParser.parseStopOnError(GruntParser.java:205)
    	at org.apache.pig.tools.grunt.Grunt.exec(Grunt.java:81)
    	at org.apache.pig.Main.run(Main.java:502)
    	at org.apache.pig.Main.main(Main.java:177)
    Caused by: org.apache.pig.impl.plan.PlanValidationException: <b><font color="#DD0000">ERROR</font></b> 1025: 
    <file aggage3.pig, line 10, column 37> Invalid field projection. Projected field [age] does not exist in schema: group:chararray,values:bag{:tuple(age:double,job:chararray,marital:chararray,education:chararray,default:chararray,balance:double,housing:chararray,loan:chararray,contact:chararray,day:double,month:chararray,duration:double,campaign:double,pdays:double,previous:double,poutcome:chararray,y:chararray)}.
    	at org.apache.pig.newplan.logical.expression.ProjectExpression.findColNum(ProjectExpression.java:191)
    	at org.apache.pig.newplan.logical.expression.ProjectExpression.setColumnNumberFromAlias(ProjectExpression.java:174)
    	at org.apache.pig.newplan.logical.visitor.ColumnAliasConversionVisitor$1.visit(ColumnAliasConversionVisitor.java:53)
    	at org.apache.pig.newplan.logical.expression.ProjectExpression.accept(ProjectExpression.java:215)
    	at org.apache.pig.newplan.DependencyOrderWalker.walk(DependencyOrderWalker.java:75)
    	at org.apache.pig.newplan.PlanVisitor.visit(PlanVisitor.java:52)
    	at org.apache.pig.newplan.logical.optimizer.AllExpressionVisitor.visit(AllExpressionVisitor.java:142)
    	at org.apache.pig.newplan.logical.relational.LOInnerLoad.accept(LOInnerLoad.java:128)
    	at org.apache.pig.newplan.DependencyOrderWalker.walk(DependencyOrderWalker.java:75)
    	at org.apache.pig.newplan.logical.optimizer.AllExpressionVisitor.visit(AllExpressionVisitor.java:124)
    	at org.apache.pig.newplan.logical.relational.LOForEach.accept(LOForEach.java:87)
    	at org.apache.pig.newplan.DependencyOrderWalker.walk(DependencyOrderWalker.java:75)
    	at org.apache.pig.newplan.PlanVisitor.visit(PlanVisitor.java:52)
    	at org.apache.pig.newplan.logical.relational.LogicalPlan.validate(LogicalPlan.java:175)
    	at org.apache.pig.PigServer$Graph.compile(PigServer.java:1767)
    	at org.apache.pig.PigServer$Graph.access$300(PigServer.java:1443)
    	at org.apache.pig.PigServer.buildStorePlan(PigServer.java:1339)
    	at org.apache.pig.PigServer.getOperatorForAlias(PigServer.java:1418)
    	at org.apache.pig.PigServer.dumpSchema(PigServer.java:806)
    	... 7 more

    Details also at logfile: C:\apps\dist\hadoop-2.7.1.2.3.3.1-21\logs\pig_1466111143557.log
    2016-06-16 21:05:49,119 [main] INFO  org.apache.pig.Main - Pig script completed in 5 seconds and 954 milliseconds (5954 ms)

    </pre><br /><b>OUT:</b><br /><pre>
    values: {age: double,job: chararray,marital: chararray,education: chararray,default: chararray,balance: double,housing: chararray,loan: chararray,contact: chararray,day: double,month: chararray,duration: double,campaign: double,pdays: double,previous: double,poutcome: chararray,y: chararray}
    gr: {group: chararray,values: {(age: double,job: chararray,marital: chararray,education: chararray,default: chararray,balance: double,housing: chararray,loan: chararray,contact: chararray,day: double,month: chararray,duration: double,campaign: double,pdays: double,previous: double,poutcome: chararray,y: chararray)}}

    </pre>



.. code:: ipython3

    %%PIG_azure aggage4.pig
    values = LOAD '$CONTAINER/centrale/bank_full_tab_no.txt' USING PigStorage('\t') AS (age:double, 
                                                        job:chararray, marital:chararray, education:chararray, 
                                                       default:chararray, balance:double, housing:chararray, loan:chararray, 
                                                       contact:chararray, day:double, month:chararray, duration:double, 
                                                       campaign:double, 
                                                       pdays:double, previous:double, poutcome:chararray, y:chararray);
    DESCRIBE values;
    gr = GROUP values BY loan ;
    DESCRIBE gr;
    agg = FOREACH gr GENERATE group, AVG(values.age) AS avg_age ;
    DESCRIBE agg;
    STORE agg INTO '$CONTAINER/centrale/bank_full_tab_no_agg2.txt' USING PigStorage('\t') ;

.. code:: ipython3

    jid = %hd_pig_submit aggage4.pig

.. code:: ipython3

    jid




.. parsed-literal::
    {'id': 'job_1466069083851_0008'}



.. code:: ipython3

    %hd_queue




.. parsed-literal::
    [{'detail': None, 'id': 'job_1466069083851_0009'},
     {'detail': None, 'id': 'job_1466069083851_0008'},
     {'detail': None, 'id': 'job_1466069083851_0007'},
     {'detail': None, 'id': 'job_1466069083851_0006'},
     {'detail': None, 'id': 'job_1466069083851_0005'},
     {'detail': None, 'id': 'job_1466069083851_0004'},
     {'detail': None, 'id': 'job_1466069083851_0003'},
     {'detail': None, 'id': 'job_1466069083851_0002'},
     {'detail': None, 'id': 'job_1466069083851_0001'}]



.. code:: ipython3

    df = %hd_job_status jid['id']
    df["status"]["state"]




.. parsed-literal::
    'RUNNING'



.. code:: ipython3

    hd_tail_stderr -n 50 jid['id']






.. raw:: html

    <pre>
    2016-06-16 21:13:19,066 [main] INFO  org.apache.hadoop.mapred.ClientServiceDelegate - Application state is completed. FinalApplicationStatus=SUCCEEDED. Redirecting to job history server
    2016-06-16 21:13:19,410 [main] INFO  org.apache.hadoop.yarn.client.api.impl.TimelineClientImpl - Timeline service address: http://headnodehost:8188/ws/v1/timeline/
    2016-06-16 21:13:19,410 [main] INFO  org.apache.hadoop.yarn.client.RMProxy - Connecting to ResourceManager at headnodehost/100.106.128.41:9010
    2016-06-16 21:13:19,410 [main] INFO  org.apache.hadoop.yarn.client.AHSProxy - Connecting to Application History server at headnodehost/100.106.128.41:10200
    2016-06-16 21:13:19,504 [main] INFO  org.apache.hadoop.mapred.ClientServiceDelegate - Application state is completed. FinalApplicationStatus=SUCCEEDED. Redirecting to job history server
    2016-06-16 21:13:19,629 [main] INFO  org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.MapReduceLauncher - 100% complete
    2016-06-16 21:13:19,629 [main] INFO  org.apache.pig.tools.pigstats.mapreduce.SimplePigStats - Script Statistics: 

    HadoopVersion	PigVersion	UserId	StartedAt	FinishedAt	Features
    2.7.1.2.3.3.1-21	0.15.0.2.3.3.1-21	hdp	2016-06-16 21:12:27	2016-06-16 21:13:19	GROUP_BY

    Success!

    Job Stats (time in seconds):
    JobId	Maps	Reduces	MaxMapTime	MinMapTime	AvgMapTime	MedianMapTime	MaxReduceTime	MinReduceTime	AvgReduceTime	MedianReducetime	Alias	Feature	Outputs
    job_1466069083851_0009	1	1	12	12	12	12	9	9	9	9	agg,gr,values	GROUP_BY,COMBINER	wasb://hdblobstorage@hdblobstorage.blob.core.windows.net//centrale/bank_full_tab_no_agg2.txt,

    Input(s):
    Successfully read 45212 records from: "wasb://hdblobstorage@hdblobstorage.blob.core.windows.net//centrale/bank_full_tab_no.txt"

    Output(s):
    Successfully stored 3 records in: "wasb://hdblobstorage@hdblobstorage.blob.core.windows.net//centrale/bank_full_tab_no_agg2.txt"

    Counters:
    Total records written : 3
    Total bytes written : 0
    Spillable Memory Manager spill count : 0
    Total bags proactively spilled: 0
    Total records proactively spilled: 0

    Job DAG:
    job_1466069083851_0009


    2016-06-16 21:13:19,848 [main] INFO  org.apache.hadoop.yarn.client.api.impl.TimelineClientImpl - Timeline service address: http://headnodehost:8188/ws/v1/timeline/
    2016-06-16 21:13:19,848 [main] INFO  org.apache.hadoop.yarn.client.RMProxy - Connecting to ResourceManager at headnodehost/100.106.128.41:9010
    2016-06-16 21:13:19,848 [main] INFO  org.apache.hadoop.yarn.client.AHSProxy - Connecting to Application History server at headnodehost/100.106.128.41:10200
    2016-06-16 21:13:19,926 [main] INFO  org.apache.hadoop.mapred.ClientServiceDelegate - Application state is completed. FinalApplicationStatus=SUCCEEDED. Redirecting to job history server
    2016-06-16 21:13:20,160 [main] INFO  org.apache.hadoop.yarn.client.api.impl.TimelineClientImpl - Timeline service address: http://headnodehost:8188/ws/v1/timeline/
    2016-06-16 21:13:20,160 [main] INFO  org.apache.hadoop.yarn.client.RMProxy - Connecting to ResourceManager at headnodehost/100.106.128.41:9010
    2016-06-16 21:13:20,160 [main] INFO  org.apache.hadoop.yarn.client.AHSProxy - Connecting to Application History server at headnodehost/100.106.128.41:10200
    2016-06-16 21:13:20,238 [main] INFO  org.apache.hadoop.mapred.ClientServiceDelegate - Application state is completed. FinalApplicationStatus=SUCCEEDED. Redirecting to job history server
    2016-06-16 21:13:20,506 [main] INFO  org.apache.hadoop.yarn.client.api.impl.TimelineClientImpl - Timeline service address: http://headnodehost:8188/ws/v1/timeline/
    2016-06-16 21:13:20,506 [main] INFO  org.apache.hadoop.yarn.client.RMProxy - Connecting to ResourceManager at headnodehost/100.106.128.41:9010
    2016-06-16 21:13:20,506 [main] INFO  org.apache.hadoop.yarn.client.AHSProxy - Connecting to Application History server at headnodehost/100.106.128.41:10200
    2016-06-16 21:13:20,582 [main] INFO  org.apache.hadoop.mapred.ClientServiceDelegate - Application state is completed. FinalApplicationStatus=SUCCEEDED. Redirecting to job history server
    2016-06-16 21:13:20,646 [main] WARN  org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.MapReduceLauncher - Encountered Warning FIELD_DISCARDED_TYPE_CONVERSION_FAILED 7 time(s).
    2016-06-16 21:13:20,646 [main] INFO  org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.MapReduceLauncher - Success!
    2016-06-16 21:13:20,725 [main] INFO  org.apache.pig.Main - Pig script completed in 1 minute, 2 seconds and 364 milliseconds (62364 ms)

    </pre><br /><b>OUT:</b><br /><pre>
    values: {age: double,job: chararray,marital: chararray,education: chararray,default: chararray,balance: double,housing: chararray,loan: chararray,contact: chararray,day: double,month: chararray,duration: double,campaign: double,pdays: double,previous: double,poutcome: chararray,y: chararray}
    gr: {group: chararray,values: {(age: double,job: chararray,marital: chararray,education: chararray,default: chararray,balance: double,housing: chararray,loan: chararray,contact: chararray,day: double,month: chararray,duration: double,campaign: double,pdays: double,previous: double,poutcome: chararray,y: chararray)}}
    agg: {group: chararray,avg_age: double}

    </pre>



.. code:: ipython3

    %blob_ls /centrale






.. raw:: html

    <div>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>name</th>
          <th>last_modified</th>
          <th>content_type</th>
          <th>content_length</th>
          <th>blob_type</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>centrale/bank_full.csv</td>
          <td>2016-06-15 22:17:59+00:00</td>
          <td>None</td>
          <td>4610348</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>1</th>
          <td>centrale/bank_full_tab.txt</td>
          <td>2016-06-15 22:19:46+00:00</td>
          <td>None</td>
          <td>3751306</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>2</th>
          <td>centrale/bank_full_tab_no.txt</td>
          <td>2016-06-15 23:00:52+00:00</td>
          <td>None</td>
          <td>3751306</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>3</th>
          <td>centrale/bank_full_tab_no_agg.txt</td>
          <td>2016-06-16 10:32:11+00:00</td>
          <td>None</td>
          <td>0</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>4</th>
          <td>centrale/bank_full_tab_no_agg.txt/_SUCCESS</td>
          <td>2016-06-16 10:32:11+00:00</td>
          <td>None</td>
          <td>0</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>5</th>
          <td>centrale/bank_full_tab_no_agg.txt/part-r-00000</td>
          <td>2016-06-16 10:32:11+00:00</td>
          <td>None</td>
          <td>49</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>6</th>
          <td>centrale/bank_full_tab_no_agg2.txt</td>
          <td>2016-06-16 21:13:14+00:00</td>
          <td>None</td>
          <td>0</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>7</th>
          <td>centrale/bank_full_tab_no_agg2.txt/_SUCCESS</td>
          <td>2016-06-16 21:13:14+00:00</td>
          <td>None</td>
          <td>0</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>8</th>
          <td>centrale/bank_full_tab_no_agg2.txt/part-r-00000</td>
          <td>2016-06-16 21:13:13+00:00</td>
          <td>None</td>
          <td>49</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>9</th>
          <td>centrale/scripts/pig/aggage.pig</td>
          <td>2016-06-15 23:15:54+00:00</td>
          <td>None</td>
          <td>782</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>10</th>
          <td>centrale/scripts/pig/aggage.pig.log</td>
          <td>2016-06-15 23:16:40+00:00</td>
          <td>None</td>
          <td>0</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>11</th>
          <td>centrale/scripts/pig/aggage.pig.log/exit</td>
          <td>2016-06-15 23:16:40+00:00</td>
          <td>None</td>
          <td>3</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>12</th>
          <td>centrale/scripts/pig/aggage.pig.log/stderr</td>
          <td>2016-06-15 23:16:30+00:00</td>
          <td>None</td>
          <td>4060</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>13</th>
          <td>centrale/scripts/pig/aggage.pig.log/stdout</td>
          <td>2016-06-15 23:16:30+00:00</td>
          <td>None</td>
          <td>0</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>14</th>
          <td>centrale/scripts/pig/aggage2.pig</td>
          <td>2016-06-16 10:28:16+00:00</td>
          <td>None</td>
          <td>853</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>15</th>
          <td>centrale/scripts/pig/aggage2.pig.log</td>
          <td>2016-06-16 10:29:04+00:00</td>
          <td>None</td>
          <td>0</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>16</th>
          <td>centrale/scripts/pig/aggage2.pig.log/exit</td>
          <td>2016-06-16 10:29:04+00:00</td>
          <td>None</td>
          <td>3</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>17</th>
          <td>centrale/scripts/pig/aggage2.pig.log/stderr</td>
          <td>2016-06-16 10:28:54+00:00</td>
          <td>None</td>
          <td>4883</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>18</th>
          <td>centrale/scripts/pig/aggage2.pig.log/stdout</td>
          <td>2016-06-16 10:28:54+00:00</td>
          <td>None</td>
          <td>613</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>19</th>
          <td>centrale/scripts/pig/aggage3.pig</td>
          <td>2016-06-16 21:05:11+00:00</td>
          <td>None</td>
          <td>853</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>20</th>
          <td>centrale/scripts/pig/aggage3.pig.log</td>
          <td>2016-06-16 21:05:59+00:00</td>
          <td>None</td>
          <td>0</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>21</th>
          <td>centrale/scripts/pig/aggage3.pig.log/exit</td>
          <td>2016-06-16 21:05:59+00:00</td>
          <td>None</td>
          <td>3</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>22</th>
          <td>centrale/scripts/pig/aggage3.pig.log/stderr</td>
          <td>2016-06-16 21:05:49+00:00</td>
          <td>None</td>
          <td>4883</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>23</th>
          <td>centrale/scripts/pig/aggage3.pig.log/stdout</td>
          <td>2016-06-16 21:05:49+00:00</td>
          <td>None</td>
          <td>613</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>24</th>
          <td>centrale/scripts/pig/aggage4.pig</td>
          <td>2016-06-16 21:11:47+00:00</td>
          <td>None</td>
          <td>861</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>25</th>
          <td>centrale/scripts/pig/aggage4.pig.log</td>
          <td>2016-06-16 21:13:31+00:00</td>
          <td>None</td>
          <td>0</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>26</th>
          <td>centrale/scripts/pig/aggage4.pig.log/exit</td>
          <td>2016-06-16 21:13:31+00:00</td>
          <td>None</td>
          <td>3</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>27</th>
          <td>centrale/scripts/pig/aggage4.pig.log/stderr</td>
          <td>2016-06-16 21:13:21+00:00</td>
          <td>None</td>
          <td>16643</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>28</th>
          <td>centrale/scripts/pig/aggage4.pig.log/stdout</td>
          <td>2016-06-16 21:13:21+00:00</td>
          <td>None</td>
          <td>654</td>
          <td>BlockBlob</td>
        </tr>
        <tr>
          <th>29</th>
          <td>centrale2/bank_full_tab_no.txt</td>
          <td>2016-06-16 10:18:58+00:00</td>
          <td>None</td>
          <td>3751188</td>
          <td>BlockBlob</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    %blob_downmerge --help


.. parsed-literal::
    usage: blob_downmerge [-h] [-o] remotepath localfile
    download a set of files from a blob storage folder, files will be merged, we
    assume the container is the first element to the remote path
    positional arguments:
      remotepath       remote path of the folder to download
      localfile        local name for the downloaded merged file
    optional arguments:
      -h, --help       show this help message and exit
      -o, --overwrite  overwrite the local file
    usage: blob_downmerge [-h] [-o] remotepath localfile
    


.. code:: ipython3

    %blob_down /centrale/bank_full_tab_no_agg2.txt/part-r-00000 agg_hadoop3.txt




.. parsed-literal::
    'agg_hadoop3.txt'



.. code:: ipython3

    import pandas
    df = pandas.read_csv("agg_hadoop3.txt", sep="\t", header=-1)
    df






.. raw:: html

    <div>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>0</th>
          <th>1</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>no</td>
          <td>41.008823</td>
        </tr>
        <tr>
          <th>1</th>
          <td>yes</td>
          <td>40.555632</td>
        </tr>
        <tr>
          <th>2</th>
          <td>loan</td>
          <td>NaN</td>
        </tr>
      </tbody>
    </table>
    </div>



J’ai oublié d’enlever le header. On vérifie que les calcus sont bons en
les faisant en local.

.. code:: ipython3

    df = pandas.read_csv("bank-full.csv", sep=";")
    df.head()






.. raw:: html

    <div>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>age</th>
          <th>job</th>
          <th>marital</th>
          <th>education</th>
          <th>default</th>
          <th>balance</th>
          <th>housing</th>
          <th>loan</th>
          <th>contact</th>
          <th>day</th>
          <th>month</th>
          <th>duration</th>
          <th>campaign</th>
          <th>pdays</th>
          <th>previous</th>
          <th>poutcome</th>
          <th>y</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>0</th>
          <td>58</td>
          <td>management</td>
          <td>married</td>
          <td>tertiary</td>
          <td>no</td>
          <td>2143</td>
          <td>yes</td>
          <td>no</td>
          <td>unknown</td>
          <td>5</td>
          <td>may</td>
          <td>261</td>
          <td>1</td>
          <td>-1</td>
          <td>0</td>
          <td>unknown</td>
          <td>no</td>
        </tr>
        <tr>
          <th>1</th>
          <td>44</td>
          <td>technician</td>
          <td>single</td>
          <td>secondary</td>
          <td>no</td>
          <td>29</td>
          <td>yes</td>
          <td>no</td>
          <td>unknown</td>
          <td>5</td>
          <td>may</td>
          <td>151</td>
          <td>1</td>
          <td>-1</td>
          <td>0</td>
          <td>unknown</td>
          <td>no</td>
        </tr>
        <tr>
          <th>2</th>
          <td>33</td>
          <td>entrepreneur</td>
          <td>married</td>
          <td>secondary</td>
          <td>no</td>
          <td>2</td>
          <td>yes</td>
          <td>yes</td>
          <td>unknown</td>
          <td>5</td>
          <td>may</td>
          <td>76</td>
          <td>1</td>
          <td>-1</td>
          <td>0</td>
          <td>unknown</td>
          <td>no</td>
        </tr>
        <tr>
          <th>3</th>
          <td>47</td>
          <td>blue-collar</td>
          <td>married</td>
          <td>unknown</td>
          <td>no</td>
          <td>1506</td>
          <td>yes</td>
          <td>no</td>
          <td>unknown</td>
          <td>5</td>
          <td>may</td>
          <td>92</td>
          <td>1</td>
          <td>-1</td>
          <td>0</td>
          <td>unknown</td>
          <td>no</td>
        </tr>
        <tr>
          <th>4</th>
          <td>33</td>
          <td>unknown</td>
          <td>single</td>
          <td>unknown</td>
          <td>no</td>
          <td>1</td>
          <td>no</td>
          <td>no</td>
          <td>unknown</td>
          <td>5</td>
          <td>may</td>
          <td>198</td>
          <td>1</td>
          <td>-1</td>
          <td>0</td>
          <td>unknown</td>
          <td>no</td>
        </tr>
      </tbody>
    </table>
    </div>



.. code:: ipython3

    df[["loan", "age"]].groupby("loan").mean()






.. raw:: html

    <div>
    <table border="1" class="dataframe">
      <thead>
        <tr style="text-align: right;">
          <th></th>
          <th>age</th>
        </tr>
        <tr>
          <th>loan</th>
          <th></th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <th>no</th>
          <td>41.008823</td>
        </tr>
        <tr>
          <th>yes</th>
          <td>40.555632</td>
        </tr>
      </tbody>
    </table>
    </div>