There's a really simple example that uses RODBC to query Hive from R. Should work in RStudio just fine, but you might need to adjust some instructions based on your Hive environment versus the HDInsight example. https://blogs.technet.microsoft.com/meacoex/2014/06/07/connecting-r-to-hdinsight-through-hive/ ... View more

@Vijay Parmar I think you'll want to use some kind of outside tool to orchestrate that series of activities, rather than trying to do it all within the Hive environment. HiveQL doesn't have the ability, by itself, to make a series of HTTP calls to external services and retrieve data from them. You could take all of these steps and script them in something like Python, and then call that Python script as a Hive UDF, but I would recommend looking at Nifi / HDF to orchestrate that process. Use QueryDatabaseTable processor to access the Teradata table that you need (via JDBC). Use EvaluateJSONPath processor to pull out the specific URL attribute in the JSON. Use Get/PostHTTP processor to make the HTTP call to get the next JSON. Use EvaluateJSONPath processor to pick out the pieces of that document that you want to write to Hive. Use PutHDFS processor to write the output into the HDFS location. Then layer an external Hive table on top of that HDFS location. You might also use some other processors in the middle there to merge content together into a single file or otherwise optimize things for the final output format. How you approach this probably depends on what tools you have on hand and how much data you're going to be running through the process and how often it has to run. ... View more

Can you say a little bit more about the text files? Are they all the same kind of data and format, or different? How big are the text files in terms of GB and number of rows/columns? ... View more

I'd recommend that you take a serious look at creating a custom NiFi processor. I found it to be a fairly straightforward exercise and wrote a recent post about it here: https://hortonworks.com/blog/apache-nifi-not-scratch/ ... View more

Just double-checking the obvious: Do you have Python installed on your Windows machine? And have you installed the petl and xlrd modules using pip? You can test the script by itself just by running it from the command line. Have you tried that? ... View more

You didn't mention if you already have a mechanism for converting your XLS files into CSV, but here's a way you might what to orchestrate all of this in NiFi. Use a ListFile processor to list all of the *.xls files in your input directory, it will output a flowfile for each xls file it finds. Route that to an ExecuteStreamCommand processor that runs a simple program to convert your XLS to CSV. I'd recommend a simple Python script that uses the petl module. Have that script write the output into the same directory. Have a separate NiFi flow that: Uses a GetFile processor to do whatever it is that you want to do with CSV files. Point it at that same input directory. Make sure you configure in the File Filter property to only pick up CSV files, though. You could use different input directories, too, of course. The trick, I think, is using ListFile and a conversion script. Here's a simple outline of a python script that should work in most cases. It assumes that you always want to convert just the first sheet. #!/usr/bin/env python import sys import petl as etl import xlrd # Pass ${absolute.path}/${filename} as a command line argument inputFile = sys.argv[1] xls = etl.fromxls(inputFile) etl.tocsv(xls, inputFile+".csv", write_header=True) ... View more