MSBI

 Hi All, Below is the code which is used to run the query from Oracle Database and load them to CSV files using Scala. object Main {     def main(args: Array[String]): Unit = {       /* Local connection details*/     val dbUser = "dbusernameo"     val dbPassword = "dbpassword"     val dbURL = "jdbc:oracle:thin:@11.2.4.80:1234:databasename"       val configQuery =       """       SELECT First_Name,Last_Name,Middle_Name,Student_Number       ,Grade_Level,Enroll_Status,Gender       from Students Where rownum<2         """     val conConfig = OracleConnect.connJdbc(dbUser, dbPassword, dbURL)       val statement = conConfig.createStatement()   ...

Hi All,   Below is the code which can be created as csv.scala file and can be called inside your MAIN Scala function to push the data from Data Frame to CSV files. import au.com.bytecode.opencsv.CSVWriter import java.time.format.DateTimeFormatter import org.apache.commons.io.FilenameUtils import java.io.FileWriter   object CSVExport {     def exportCSVFile (oracleUser: String,oraclePassword: String,oracleURL: String,oracleOutPutFilePath:String,oracleOutPutFileDateFormat:String,oracleQueryFilePath:String): Unit = {       /* Read date part from the date pattern*/     val dateNow: String = DateTimeFormatter.ofPattern(oracleOutPutFileDateFormat).format(java.time.LocalDate.now)       /* Reconstruct the new file name*/     val basename = FilenameUtils.getBaseName(oracleOutPutFilePath)     val extension = FilenameUtils....

 Hi All, We can use, below code to create a function in SCALA to connect to Oracle Database and call this function on your main SCALA by passing the parameter.  Please be sure to install the necessary drivers - oracle JDBC pool drivers   def connJdbc (oracleUser: String,oraclePassword: String,oracleURL: String): java.sql.Connection = {     val ods = new OracleDataSource()     ods.setUser(oracleUser)     ods.setURL(oracleURL)     ods.setPassword(oraclePassword)     ods.getConnection()   } }  

When you install pymssql module using pip in python and then try to import pymssql, you may end up with “ PyMSSql - import _mssql - ImportError: DLL load failed: The specific module could not be found ” error, if you are using pymssql version 2.1 or greater. This is due to security reason, pymssql now is not linked with SSL and FreeTDS, unlike previous version where SSL and FreeTDS were jointly linked with pymssql during installation. This information can be found on pymssql site found http://pymssql.org/en/latest/freetds.html#windows In order to overcome, we need to install supporting components FreeTDS and OpenSSL independently and then pymssql will work without any issue. Below are the steps to download and configure FreeTDS and OpenSSL. FreeTDS can be downloaded https://github.com/ramiro/freetds/releases And extract the file which is download. Now place the extract folder where your python module is installed.  (Can be kept anywhere but to avoid accide...

The node /hbase is not in ZooKeeper. It should have been written by the master. Check the value configured in 'zookeeper.znode.parent'. There could be a mismatch with the one configured in the master. If you come across this error, while starting Hbase, then, check Hbase-Site.XML file (For me it was in /usr/lib/hbase-0.96.2-hadoop2/conf folder). Check if Znode is present else add this property to existing XML node. <configuration>         <property>                 <name>zookeeper.znode.parent</name>                 <value>/hbase-unsecure</value>         </property> </configuration> This is done for Stand-Alone mode. I am not sure how it is done for clusters.

I had a SSIS package where I was calling a stored procedure in OLEDB Source and it was returning a “The Value Was Too Large to Fit in the Output Column” error. Well, My Datatype in OLEDB source was matching with my OLEDB Destination table. However, when I googled, we got solutions like to increase the output of OLEDB Source using Advanced Editor option . I was not at all comfortable with their solution as my source, destination and my intermediate transformation all are having same length and data type and I don’t want to change. Then I found that I was missing SET NOCOUNT ON option was missing in Stored Procedure. Once I added it, my data flow task ran successfully. 

The back end components of Hadoop system can be visualized as shown below. Name Node and Data Node will be explained in detail in my next blog. All these Daemons are nothing but a piece of code. Java code is running at the background. In order to run Java Code, we need JVM, So each daemon service need some JVM service to run. Job Tracker- Any operation can be considered as a Job, example Read a text file is a job. This is handled by Job Tracker. Task tracker- A job can have many tasks. Like connection to file is one of the task, Reading the data is other task, displaying/processing the data is another task. These are managed by Task Tracker.

Pictorial Representation of Hadoop Eco Systems is as shown below. YARN system are not present in first generation of Hadoop development. (Hadoop 1.x versions). Remember, we do not have Yarn Cluster Resource Management System in Hadoop 1.x version which was a disadvantage as any other operations on HDFS, has to be converted to MR code (Map-Reduce Algorithm) and then it use to process the data. With help of YARN (Yet Another Resource Negotiator) in place, we can process HDFS files directly without converting it to into MR code, with the help of some additional languages such as Spark, Giraffe etc.,

Data Life Cycle The data life cycle is pictorial defined as show below:     As we see, in our current system, we capture/ Extract our data, then we store it and later we process for reporting and analytics. But in case of big data, the problem lies in storing and then processing it faster. Hence Hadoop takes this portion, where it stores the data in effective format (Hadoop distributed File System) and also process using its engine (Map Reduce Engine). Since Map Reduce engine or Hadoop engine need data on HDFS format to process, We have favorable tools available in market to do this operation. As an example, Scoop is a tool which converts RDBMS to HDFS. Likewise we have SAP BOD to convert sap system data to HDFS.

Understanding Big Data We all in recent time, came across the word ‘Big Data’. So the question is what exactly is Big Data? How much TB or GB or data is called a Big Data? Well, there is no standard size definition for Big Data. If current system when not able to handle the data, then, we call such data as Big Data. (Big Data is just a terminology used in IT) As an example, if I take a text file of 50 GB, Processing a text file of 50 GB size on our Laptop or computer is not a huge task but if we take a smart phone, processing 10 GB of data is huge task. That means, for mobile phone, that 50 GB of data is Big Data. Understanding Hadoop Our current systems such as ETL tools, reporting tools, programming environment all have capability of handling few petabyte of Data. And the growth of data annually is shown below in chart And also the growth of unstructured, Semi structured data are increasingly every day. So there is a need of more adv...

Hi All, Recently got into a situation of grouping set of data with a comma delimited. Here is the requirement. I have a table like this below,     And the way data should be shown as So let us create a sample data set to achieve this: create table #t1 ( ID INT , Name Varchar ( 10 )) Insert into #t1 Select 1 , 'a' Union Select 1 , 'b' Union Select 1 , 'c' Union Select 2 , 'e' Union Select 2 , 'f' Union Select 2 , 'a' Union Select 2 , 'H' Union Select 3 , 'X' And query for the output SELECT   ID        , STUFF (( SELECT ', ' + CAST ( Name  AS VARCHAR ( 10 ))          FROM #t1          WHERE ID = t . ID          FOR XML PATH ( '' ), TYPE ). value ( '.' , 'NVARCHAR(MAX)' ), 1 , 2 , ' ' ...

Recently I had copied a excel file for analysis from SFTP site to development server from my account and asked my team mate to analyze it, by loading it into table. He logged in to the server and used a simple data flow task in SSIS with source as Excel to load the data. When he was connecting excel using excel connection manager, he had a weird error. Unexpected error from external database driver ()  He was unsure of why he was getting the error when selecting the sheet names. Then when reported, we quickly got into the possible reason of security. As I copied the file from SFTP to Development machine using my account, it had limited the access to other users. When he was trying to load, he was facing the error because it was a read-only file for him. Soon I granted full permission to him on excel security and it worked for him. There are various different solution on internet but initially none worked for us when we were trying to resolve. Hope this so...

We can able to maintain old historical and current data (SCD) using the date column. Let me show you people how to write a small query to achieve it. For example, Assume we have a column called currency which has currency values of all the countries and your business needs it to keep it uniform, say convert all currency of different countries to USD, so it will be easy for Business judgement and analysis. For this, let me take an example of Yahoo! Finance . Yahoo! Finance API provides us currency conversion of all country based on time. I am not here going to tell how to pull the data from Yahoo! Finance API, But I am using such data which I pulled to explain how SCD can be achieved. Below is a image, which depicts, the data which we got from Yahoo! Finance. We wanted this to get converted as shown below: Where we wanted to derive Effective_To column, which indicates, from one date to other date, the currency was so & so for a country against USD. ...

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." When you receive this error, Please verify if TABLE LOCK option is ticked in OLEDB Destination table. By Checking the TABLE LOCK option, this error will be resolved.

Hi All, Many situation when we are pulling the data from website or calling the data through an API, we end up with JSON data. If we needed to load JSON Data into Database, we need to break the JSON data into columns. We will see how to break the JSON data using C#. Consider below JSON Data. {     "requestId":"299f#150942d9c47",    "result":[        {           "id":10357,          "leadId":3032,          "activityDate":"2015-06-16T20:20:36Z",          "activityTypeId":1,          "primaryAttributeValueId":26,          "primaryAttributeValue":"eos-celebration",          "attributes":[   ...