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
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
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
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 -
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.
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.
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-
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.
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.
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 Keyconstraint 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.
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
KeyConstraints -
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
ADDConstraint
FK_Emp_DeptId Foreign
Key (DeptId)
References
Tbl_Department(DeptId)
---- drop
Foreign Key Constraint
ALTER
TABLE TBL_EMPLOYEE
DropConstraint
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 –
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.
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
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.
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 -
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 Keyconstraint 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
ADDConstraint
PK_Emp_EmpId Primary
Key(EmpId)
---- drop
Primary Key Constraint
ALTER
TABLE TBL_EMPLOYEE
DropConstraint
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 -
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.
A scatter chart displays a series as a set of points.
Values are represented by the position of the points on the chart.
Categories are represented by different markers on the chart.
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-
How to create a Scatter Plot/Bubble Chart
How to change the Chart Title, Legend Position, Legend Title, and Font style
How to change marker shapes on the chart
How to add Tool-tips text on the markers
To watch a demo, please visit our YouTube channel-