Monday, August 12, 2019

Talend ETL - Move NoSQL MongoDB Data into Azure SQL Database

Here you will learn, How to Move NoSQL MongoDB Data into Azure SQL Database Table by using Talend Open Studio.
There are ways to transfer/sync data from mongodb to sql-server and Mongodb contains collections instead of table and the data is stored differently.

There is no way to directly move data from MongoDB to SQL Server. Because MongoDB data is non-relational, any such movement must involve defining a target relational data model in SQL Server, and then developing a transformation that can take the data in MongoDB and transform it into the target data model.

Most ETL tools such as Kettle or Talend can help you with this process, or if you're a glutton for punishment, you can just write gobs of code.



Keep in mind that if you need this transformation process to be online, or applied more than once, you may need to tweak it for any small changes in the structure or types of the data stored in MongoDB. As an example, if a developer adds a new field to a document inside a collection, your ETL process will need rethinking (possibly new data model, new transformation process, etc.).

Process of data flow -
Creating Azure SQL DB Connection - Connecting to an Azure SQL Database
Creating NoSQL Connection - Connecting to NoSQL MongoDB 
Pull data from NoSQL MongoDB collections
Showing Data of from NoSQL MongoDB
Load Data into Azure SQL Database

List of Talend components-
1. tMongoDBInput - Connecting to NoSQL MongoDB and pull the requested documents
2. tLogRow – allows us to write data, that is flowing through our Job (rows), to the console
3. tDBOutput -  Connecting with Azure SQL Database and load data into Azure SQL Database Table

To Learn more, please visit our blog at - 
http://www.sql-datatools.com

To Learn more, please visit our YouTube channel at - 
http://www.youtube.com/c/Sql-datatools

Friday, August 9, 2019

Talend ETL - How to load any files into Azure Tables Storage

Here is an example of using Talend components to connect to a Microsoft Azure storage account that gives you access to Azure storage table service, write some yearly sales data into an Azure storage table.


Process flow -
•Creating Azure Storage Connection - Connecting to an Azure storage account
•Creating File Delimited Connection - Connecting to Raw Data File
•Showing Data of Raw Data File
•Load Data into Azure Storage Tables

List of Talend components -
1.tAzureStorageConnection - to connecting to an Azure storage account
2.tInputFileDelimited – Pull the data from the Raw data file and pass to another component
3.tLogRow – allows us to write data, that is flowing through our Job (rows), to the console
4.tAzureStorageOutputTable  - Performs the defined action on a given Azure storage table and inserts, replaces, merges or deletes entities in the table based on the incoming data from the preceding component
a.Partition Key -Select the schema column that holds the partition key value from the drop-down list.
b.Row Key - Select the schema column that holds the row key value from the drop-down list.
Microsot Azure Storage Explorer - To connect with Azure Storage Account and go through all the services over there.

To Learn more, please visit our blog at - 
http://www.sql-datatools.com

To Learn more, please visit our YouTube channel at - 
http://www.youtube.com/c/Sql-datatools

Friday, July 12, 2019

Talend ETL - Download Or Retrieve Files from Azure Blob Storage Container

In this tutorial,  we will learn "How to Download Or Retrieve Files from Azure Blob Storage Container" in Talend Open Studio.
We are amusing that you have already an Azure account and have all the credentials to connect from Talend Open Studio to Azure Storage Blob.

Here, we will focus on Talend - Azure Storage Blob
Creating Azure Storage Connection - Setting for connecting to an Azure storage account
  • Account name -Enter the name of the storage account to be connected to.
  • Account key - Enter the primary or the secondary key associated with the storage account to be used. These keys can be found in the Manage Access Key dashboard in the Azure Storage system to be connected to.
  • Protocol list - select the protocol for the endpoint of the storage account to be used.


Talend – Retrieve/Download files from Azure Storage Blob Container

Creating Azure Storage Connection - Connecting to an Azure storage account
List of Files on Azure Storage Blob Container
Get Azure Storage Blob Container and set local folder/directory path
Showing name of the files

List of Talend components-
1. tAzureStorageConnection - to connecting to an Azure storage account
2. tAzureStorageList  - to get the list of files from Azure storage blob container and directory name in Azure storage blob container
3. tAzureStorageGet – To provide the file path from local, Azure storage blob container name and directory name in Azure storage blob container
4. tJava – To show the output message

To Learn more, please visit us at YouTube -


Talend ETL - Upload any files to Azure Blob Storage

In this tutorial, we will learn "How to upload any files to Azure Blob Storage Container" in Talend Open Studio. 
We are amusing that you have already an Azure account and have all the credentials to connect from Talend Open Studio to Azure Storage Blob.

Note: If you don't have an Azure subscription, then create a free account before you begin.

Here, we will focus on Talend - upload any files to Azure Storage Blob 
Creating Azure Storage Connection - Setting for connecting to an Azure storage account
Account name -Enter the name of the storage account to be connected to. 
Account key - Enter the primary or the secondary key associated with the storage account to be used. These keys can be found in the Manage Access Key dashboard in the Azure Storage system to be connected to.
Protocol list - select the protocol for the endpoint of the storage account to be used. 

Set local folder path to upload files To provide the file path from local, Azure storage blob container name and directory name in Azure storage blob container.
Get the list of files from Azure storage blob container and directory name in Azure storage blob container
List of Talend components to accomplish this task-
1. tAzureStorageConnection - to connecting to an Azure storage account
2. tAzureStoragePut – To provide the file path from local, Azure storage blob container name and directory name in Azure storage blob container
3. tAzureStorageList  - to get the list of files from Azure storage blob container and directory name in Azure storage blob container
4. tJava – To show the output message 

To Learn more, please visit us at YouTube - 

Wednesday, July 10, 2019

Talend ETL - Creating Azure Storage Blob Container

http://www.sql-datatools.com
In this tutorial, we will learn "How to create Azure Storage Blob Container" in Talend Open Studio. 
We are amusing that you have already an Azure account and have all the credentials to connect from Talend Open Studio to Azure Storage Blob.

Note: If you don't have an Azure subscription, then create a free account before you begin.

Here, we will focus on Talend - Azure Storage Blob
Creating Azure Storage Connection - Setting for connecting to an Azure storage account
Account name -Enter the name of the storage account to be connected to. 
Account key - Enter the primary or the secondary key associated with the storage account to be used. These keys can be found in the Manage Access Key dashboard in the Azure Storage system to be connected to.
Protocol list - select the protocol for the endpoint of the storage account to be used. 

Creating a container in Azure Blob Storage - Here, you have to set up the Azure storage connection. 
Fill Container name, you need to create. If a container using the same name exists, that container will be overwritten at run-time.
Access control - select the access restriction level for the container to be created
Check existence of the container- setup the Azure storage connection and provide the name of the container which you want to check for existence. If Azure blob storage container is exists then it will return True otherwise False.



Caputre the output message 

List of Talend components-
1. tAzureStorageConnection - to connecting to an Azure storage account
2. tAzureStorageContainerCreate – to creating a container
3. tAzureStorageContainerExist  - to Verifying the creation
4. tJava – To show the output message 


To Learn more, please visit us at YouTube -

Tuesday, July 9, 2019

Azure SQL Server - Configuring Connections with PowerBI

In this tutorial, we will learn "How to configure connections with Power BI" to connect with Azure SQL database or Azure SQL data warehouse from Azure SQL Server.

Note: If you don't have an Azure subscription, then create a free account before you begin.


You have to do followings-
1. Configure Client IP - We have to Add to the Allowed IP Addresses in set server firewall settings and where we can also add a range of IP addresses to allow.
2. Configure Connection String with access credentials - get the connection string to connect with.
3. Connect DbVisualizer with credentials with Azure 
4. Pull your data from database or data warehouse

Note: You don't use your Azure login ID to connect to the SQL service. You use the new credentials that are specific to the SQL database you've created via the Azure SQL Data Service console. Also, you must specify "encryptionMethod=SSL". Required for all communications with SQL Azure.

To learn more, please visit us at YouTube -

Azure SQL Server - Configuring Connection with DbVisualizer

In this tutorial, we will learn "How to configure connections with DbVisualizer tools " to connect with Azure SQL database or Azure SQL data warehouse from Azure SQL Server.

Note: If you don't have an Azure subscription, then create a free account before you begin.

You have to do followings-
1. Configure Client IP - We have to Add to the Allowed IP Addresses in set server firewall settings and where we can also add a range of IP addresses to allow.
2. Configure Connection String with access credentials - get the connection string to connect with.
3. Connect DbVisualizer with credentials with Azure 
4. Pull your data from database or data warehouse

Note: You don't use your Azure login ID to connect to the SQL service. You use the new credentials that are specific to the SQL database you've created via the Azure SQL Data Service console. Also, you must specify "encryptionMethod=SSL". Required for all communications with SQL Azure.

To Learn more, please visit us at YouTube - 

Azure Server - Configuring Connection with Excel

In this tutorial, we will learn "How to configure connections with MS EXCEL " to connect with Azure SQL database or Azure SQL data warehouse from Azure SQL Server.

Note: If you don't have an Azure subscription, then create a free account before you begin.

You have to do followings-
1. Configure Client IP - We have to Add to the Allowed IP Addresses in set server firewall settings and where we can also add a range of IP addresses to allow.
2. Configure Connection String with access credentials - get the connection string to connect with.
3. Connect DbVisualizer with credentials with Azure 
4. Pull your data from database or data warehouse

Note: You don't use your Azure login ID to connect to the SQL service. You use the new credentials that are specific to the SQL database you've created via the Azure SQL Data Service console. Also, you must specify "encryptionMethod=SSL". Required for all communications with SQL Azure.

To Learn more, please visit us at YouTube -

Azure Server - Configuring Connections with Talend Open Studio

In this tutorial, we will learn "How to configure connections with Talend Open Studio ETL tools" to connect with Azure SQL database or Azure SQL data warehouse from Azure SQL Server.

Note: If you don't have an Azure subscription, then create a free account before you begin.


You have to do followings-
1. Configure Client IP - We have to Add to the Allowed IP Addresses in set server firewall settings and where we can also add a range of IP addresses to allow.
2. Configure Connection String with access credentials - get the connection string to connect with.
3. Connect DbVisualizer with credentials with Azure 
4. Pull your data from database or data warehouse

Note: You don't use your Azure login ID to connect to the SQL service. You use the new credentials that are specific to the SQL database you've created via the Azure SQL Data Service console. Also, you must specify "encryptionMethod=SSL". Required for all communications with SQL Azure.

To Learn more with demo, please visit us at YouTube- 

Azure Server - How to configure the firewall settings for Client Access

In this tutorial, we will learn "How to configure the firewall settings for Client/Users Access" in Azure SQL Server. 
How can we provide access to the clients/users Azure SQL Databases or data warehouses.  I wanted to go through and show what options you have to configure the firewall to allow access to known clients.  

Note: If you don't have an Azure subscription, then create a free account before you begin.

You have to do followings-
1. Configure Client IP - We have to Add to the Allowed IP Addresses in set server firewall settings and where we can also add a range of IP addresses to allow.

You can allow client IP for any specific Azure SQL database or Azure SQL data warehouse. 

To Learn more, please visit us at YouTube

Thursday, July 4, 2019

Talend ETL - How to Aggregating values and Sorting data

Actually, tAggregateRow receives a flow and aggregates it based on one or more columns. For each output line, are provided the aggregation key and the relevant result of set operations (min, max, sum etc.).

If you are using Talend ETL tool for processing your data and you want to aggregated and sorted data from the incoming data file then you can use tAggregateRow component which comes in Processing components category to help you to perform all types of processing tasks on data flows, including aggregations.

This component handles flow of data therefore it requires input and output, hence is defined as an intermediary step. Usually the use of tAggregateRow is combined with the tSortRow component to sort your data output.


Aggregating values and sorting data- This example shows you how to use Talend processing components to aggregate the users' comprehensive scores and then sort the aggregated scores based on the users names.

Steps to be follow -
1. Creating a Job for aggregating and sorting data
2. Creating Raw File Connections and metadata
3. Configure iFileInputDelimited to connect with File delimited metadata
4. Configure tAggregateRow component, a tSortRow component, and a tLogRow
5. Aggregate the rows with a tAggregateRow, grouping by user name, selecting the score value for each of the value rows - but make sure to ignore the nulls.
6. Executing the Job to aggregate and sort data

Wednesday, July 3, 2019

SQL Server - How to create Foreign Key constraints?

Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables.
The basic fundamental is that A Not null constraint restrict the insertion of null values into a column. 
Foreign Key Constraint is another feature of SQL which is also known as referential integrity constraint and responsible to maintain the integrity relationship between two tables. Hence, a Foreign Key is a field in a data table that is a Primary key in another table. A table can have more than one foreign key and can have multiple referential relationships to more than one table also.
They cannot accept null but can have multiple duplicate data as well.

How can we create Foreign Key constraints?
A table can have more than one foreign key and we can create this constraint at two levels -
Column Level Foreign Key Constraints - We can define Foreign Key constraint with CREATE TABLE statement or in time with table definition. 
For example, the below query creates a table Employee with the column fields EmpId with Primary Key constraint, FirstName, LastName and Age as NOT NULL but MobileNo column with Unique constrain, IsActive column with Default constraint and DeptId with Foreign Key Constraint references Department table at DeptId column . Thus, Age column has some specific check constraint to validate the data before inserting into the data table.
---- Column Level Primary Key Constraint
CREATE TABLE TBL_DEPARTMENT
(
DeptId int NOT NULL Primary Key,
DeptName varchar(25) NOT NULL,
IsActive Bit Default(1)
)
---- Insert values into table
Insert Into TBL_DEPARTMENT
(DeptId, DeptName) Values(101,’Sales & Marketing’)

---- Column Level Foreign Key Constraint
CREATE TABLE TBL_EMPLOYEE
(
EmpId int NOT NULL Primary Key,
FirstName varchar(25) NOT NULL,
LastName varchar(25) NOT NULL,
Age int NOT NULL CHECK(Age>=18),
MobileNo Bigint Unique,
IsActive Bit Default(1),
DeptId int Foreign Key References Tbl_Department(DeptId)
)

---- Insert values into table
INSERT INTO TBL_EMPLOYEE
(EmpId, FirstName, LastName, Age, MobileNo,DeptId)
VALUE
(101,’Ryan’,’Arjun’,22,7856124580,101)

Keep in your mind that if you are not going to assign a name for primary key constraint, Foreign Key Constraint, unique constraint, check constraint and default constraint then system/SQL server will generate system defined constraint names for the primary key, Foreign Key, unique, check and default constraint column.

Table Level Foreign Key Constraints -
You can create a Foreign Key constraint after creating the table with the help of Alter Table command as given below.
---- Table Level - Foreign Key being added to another column
ALTER TABLE TBL_EMPLOYEE
ADD Constraint  FK_Emp_DeptId Foreign Key (DeptId)
References Tbl_Department(DeptId)

---- drop Foreign Key Constraint
ALTER TABLE TBL_EMPLOYEE
Drop Constraint  FK_Emp_DeptId



What happen, if you have some data into your table?
If you have some data into your tables, for example employee table is in relation with department table with the help of deptId foreign key –
  1. If you want to delete some records from department table and these records are referenced to employee table then SQL Server will not allow this operation and will through errors.
  2. If you want to delete some records from the department table and these records are referenced to employee table then you should remove all the referenced data from the employee table. After that, you can remove these records from department table
  3. If you want to drop department table containing referenced to employee table based on deptId foreign key, then you have to forcefully remove this relationship from employee table by dropping the foreign key deptId.
  4. You cannot insert null value for a foreign key column because it would be primary key in the another table and primary key never support null values.
To watch a live demo, please visit our YouTube Channel -

SQL Server - How to create Primary Key Constraint?

Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables.
The basic funda is that A Not null constraint restrict the insertion of null values into a column. 
Primary Key Constraint is another feature of SQL which uniquely identifies each row in the table. It must contain unique value and it must not contain null value. Usually Primary Key is used to index the data inside the table and a table can have only one primary key but you can create a primary key on one or more of the columns of a table.

How can we create Primary Key constraints?
A table can have a Primary Key Constraint. We can create Primary Key constraints at two levels -
Column Level Primary Key Constraints - We can define Primary Key constraint with CREATE TABLE statement or in time with table definition. 
For example, the below query creates a table Employee with the column fields EmpId with Primary Key constraint, FirstName, LastName and Age as NOT NULL but MobileNo column with Unique constraint and IsActive column with Default value. Thus, Age column has some specific check constraint to validate the data before inserting into the data table.

---- Column Level Primary Key Constraint
CREATE TABLE TBL_EMPLOYEE
(
EmpId int NOT NULL Primary Key,
FirstName varchar(25) NOT NULL,
LastName varchar(25) NOT NULL,
Age int NOT NULL CHECK(Age>=18),
MobileNo Bigint Unique,
IsActive Bit Default(1)
)

---- Insert values into table
INSERT INTO TBL_EMPLOYEE
(EmpId, FirstName, LastName, Age, MobileNo)
VALUE
(101,’Ryan’,’Arjun’,22,7856124580)

Keep in your mind that if you are not going to assign a name for primary key constraint, unique constraint, check constraint and default constraint then system/SQL server will generate static constraint names for the primary key, unique, check and default constraint column.

Table Level Primary Key Constraints -
You can create a primary key constraint after creating the table with the help of Alter Table command as given below.

---- Table Level - Primary Key being added to another column
ALTER TABLE TBL_EMPLOYEE
ADD Constraint  PK_Emp_EmpId Primary Key (EmpId)

---- drop Primary Key Constraint
ALTER TABLE TBL_EMPLOYEE
Drop Constraint  PK_Emp_EmpId



What happen, if you have some data into your table?
If you have some data into your table, for example in EmpId field you have data such as Null and you want to apply primary key constraint on this column. Then you cannot create Table level primary key constraint because primary key constraint will not allow any null value. 

To watch a live demo, please visit our YouTube Channel -

SSRS - How to add a Scatter Plot in Report

Scatter Plot in SSRS is useful to visualize the relationship between any two sets of data. Scatter plots or bubble charts are commonly used for displaying and comparing numeric values, such as scientific, statistical, and engineering data.    
  1. A scatter chart displays a series as a set of points.   
  2. Values are represented by the position of the points on the chart.
  3. Categories are represented by different markers on the chart.
  4. Used to compare aggregated data across categories. 

Why Scatter Plot OR Bubble Charts Reports?
  • Use the scatter chart when you want to compare large numbers of data points without regard to time. The more data that you include in a scatter chart, the better the comparisons that you can make.
  • The bubble chart requires two values (top and bottom) per data point.
  • Scatter charts are ideal for handling the distribution of values and clusters of data points. This is the best chart type if your data-set contains many points (for example, several thousand points).
  • Displaying multiple series on a point chart is visually distracting and should be avoided. In this scenario, consider using a line chart. By default, scatter charts display data points as circles. If you have multiple series on a scatter chart, consider changing the marker shape of each point to be square, triangle, diamond, or another shape.
We will learn-
  1. How to create a Scatter Plot/Bubble Chart
  2. How to change the Chart Title, Legend Position, Legend Title, and Font style
  3. How to change marker shapes on the chart 
  4. How to add Tool-tips text on the markers
To watch a demo, please visit our YouTube channel-

Popular Posts

Get Sponsored by Big Brands