Wednesday, August 28, 2019

SQL Server - FORMAT() function in T-SQL

In SQL Server 2012 and later, there is the FORMAT() function which is responsible to format a value with the specified format such as format date/time values and number values.
Before this function,  developers usually format the values as a percentage manually.
This function accepts three arguments as given below syntax- 

FORMAT(value, format, culture)

Parameter Description
value:  Required. The value to be formatted
format:  Required. The format pattern
culture:  Optional. Specifies a culture (from SQL Server 2017)

examples -
  1. Country Specific Date Conversation,
  2. Custom Numeric Format- This consists of a series of custom numeric format specifiers that provide a pattern for your number.
  3. Format Number to Percentage - If you are trying to simply format a number as a percent with two decimal places

Example  - The following example returns a simple date formatted for different cultures.
DECLARE @d DATETIME = '10/01/2019'
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'USA' 
      ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain' 
      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German' 
      ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'China';
 
USA
Great Britain
German
China
10/1/2019
01/10/2019
01.10.2019
2019/10/1

SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result' 
      ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result' 
      ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result' 
      ,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result'; 
USA
Great Britain
German
China
Saturday, October 01, 2019
01 October 2019
Samstag, 1. Oktober 2019
2011101



Benefit of this is you can control additional things like thousand separators and you don't get that space between the number and '%'.
Remarks
  1. FORMAT returns NULL for errors other than a culture that is not valid. For example, NULL is returned if the value specified in format is not valid.
  2. The FORMAT function is nondeterministic.
  3. FORMAT relies on the presence of the .NET Framework Common Language Runtime (CLR).
  4. This function cannot be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR, could cause an error on the remote server.
  5. FORMAT relies upon CLR formatting rules, which dictate that colons and periods must be escaped. Therefore, when the format string (second parameter) contains a colon or period, the colon or period must be escaped with backslash when an input value (first parameter) is of the time data type. 

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

To Learn more, please visit our Instagram account at -
https://www.instagram.com/asp.mukesh/

To Learn more, please visit our twitter account at -
https://twitter.com/macxima

Talend ETL - Move Azure SQL Data to NoSQL MongoDB

Here you will learn, How to Move Azure SQL Data to NoSQL MongoDB 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 - Move Azure SQL Database Data into NoSQL MongoDB 
Creating Azure SQL DB Connection - Connecting to an Azure SQL Database
Creating NoSQL Connection - Connecting to NoSQL MongoDB 
Pull data from Azure SQL Database table
Showing Data of from Azure SQL Database  
Load Data into NoSQL MongoDB
List of Talend components-
1. tDBInput -  Connecting with Azure SQL Database and pull data from Azure SQL Database Table
2. tLogRow – allows us to write data, that is flowing through our Job (rows), to the console
3. tMongoDBOutput - Connecting to NoSQL MongoDB and load data into collection

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

To Learn more, please visit our Instagram account at -
https://www.instagram.com/asp.mukesh/

To Learn more, please visit our twitter account at -
https://twitter.com/macxima

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





Popular Posts