November 26 ° , 2016

Upload data on Azure SQL Database
using Azure Messaging, Stream Analytics & Logic Apps
Salvatore Pellitteri
@pellittsa
https://pellitterisbiztalkblog.wordpress.com/
November 26°, 2016
#sqlsatParma
#sqlsat566
Sponsors
November 26°, 2016
#sqlsatParma
#sqlsat566
Organizers
getlatestversion.it
November 26°, 2016
#sqlsatParma
#sqlsat566
Salvatore Pellitteri | @pellittsa
•
•
•
•
November 26°, 2016
#sqlsatParma
#sqlsat566
Agenda
•
•
– Event Hubs
– Stream Analytics
•
– Service Bus
– Logic Apps
November 26°, 2016
#sqlsatParma
#sqlsat566
November 26°, 2016
#sqlsatParma
#sqlsat566
Introduzione
– SQL Server Integration Services
– BCP
– Bulk Copy API
– …
- “Consideriamo soluzioni alternative”
November 26°, 2016
#sqlsatParma
#sqlsat566
•
– IOT
– Application Integration
– B2B Integration
– …
– In generale piccoli pacchetti di dati ma frequenti
November 26°, 2016
#sqlsatParma
#sqlsat566
•
– Upload dataset di sorgenti
dati eterogenee e distribuite
geograficamente
– Upload di flat file con
schemi complessi
November 26°, 2016
#sqlsatParma
#sqlsat566
UPLOAD DATASETS SU SQL
DATABASE
November 26°, 2016
#sqlsatParma
#sqlsat566
Layers
On-Prem
Custom
Code
November 26°, 2016
Az Event
Hubs
Az Stream
Analytics
Az SQL
Database
#sqlsatParma
#sqlsat566
Azure Event Hubs
November 26°, 2016
#sqlsatParma
#sqlsat566
Azure Event Hubs
«Microsoft Azure Event Hubs is a managed
platform service that provides a foundation for
large-scale data intake in a broad variety of
scenarios»
November 26°, 2016
#sqlsatParma
#sqlsat566
Azure Event Hubs
 Enterprise messaging scenarios with sophisticated capabilities





sequencing
dead-lettering
transaction support
strong delivery assurances
high throughput and processing flexibility for event streams
November 26°, 2016
#sqlsatParma
#sqlsat566
Azure Event Hubs
 Partizioni
Event Hub
Partition 1
Partition 2
Partition N
November 26°, 2016
#sqlsatParma
#sqlsat566
using System.Threading;
using Microsoft.ServiceBus.Messaging;
...
string connectionString = "{Connection String}";
string eventHubName = "{Event Hub Name}";
var eventHubClient = EventHubClient.CreateFromConnectionString(connectionString, eventHubName);
eventHubClient.Send(new EventData(Encoding.UTF8.GetBytes({Data}))); // Max 256KB
eventHubClient.Close();
https://azure.microsoft.com/en-us/documentation/articles/event-hubs-csharp-ephcs-getstarted/
November 26°, 2016
#sqlsatParma
#sqlsat566
November 26°, 2016
#sqlsatParma
#sqlsat566
November 26°, 2016
#sqlsatParma
#sqlsat566
Input
• Event Hub
• IoT Hub
• Blob Storage
November 26°, 2016
Query
<>
Output
•
•
•
•
•
•
•
•
SQL Database
Blob Storage
Event Hub
Table Storage
Service Bus *
DocumentDB
Power BI
Data Lake Store
#sqlsatParma
#sqlsat566
Job Input
Job Output
Job Query
November 26°, 2016
#sqlsatParma
#sqlsat566
DEMO
November 26°, 2016
#sqlsatParma
#sqlsat566
UPLOAD FLAT FILE SU SQL
DATABASE
November 26°, 2016
#sqlsatParma
#sqlsat566
November 26°, 2016
#sqlsatParma
#sqlsat566
Layers
On-Prem
Custom
Code
November 26°, 2016
Az Service
Bus
Az Logic
Apps
Az SQL
Database
#sqlsatParma
#sqlsat566
Una coda con un meccanismo di publish-and-suscribe
November 26°, 2016
#sqlsatParma
#sqlsat566
•
•
•
Message Publisher
November 26°, 2016
Queue
Message
Consumer
#sqlsatParma
#sqlsat566
using Microsoft.ServiceBus.Messaging;
...
string connectionString = "{Connection String}";
string queueName = "{Queue Name}";
var queueClient = QueueClient.CreateFromConnectionString(connectionString, queueName);
// Max 256KB Standard Subscription – Max 1MB Premium Subscription
queueClient.Send(new BrokeredMessage({data}));
queueClient.Close();
https://azure.microsoft.com/en-us/documentation/articles/service-bus-dotnet-get-started-with-queues/
November 26°, 2016
#sqlsatParma
#sqlsat566
November 26°, 2016
#sqlsatParma
#sqlsat566
November 26°, 2016
#sqlsatParma
#sqlsat566
•
– Workflow
– Trigger
– Actions
– Managed Connectors
– Enterprise Integration Pack
November 26°, 2016
#sqlsatParma
#sqlsat566
Upload flat file schema
Upload sql procedure parameter schema
Upload transformation
November 26°, 2016
#sqlsatParma
#sqlsat566
DEMO
November 26°, 2016
#sqlsatParma
#sqlsat566
Risorse
https://azure.microsoft.com/en-us/services/event-hubs/
https://azure.microsoft.com/en-us/services/stream-analytics/
https://azure.microsoft.com/en-us/services/service-bus/
https://azure.microsoft.com/en-us/services/logic-apps/
https://pellitterisbiztalkblog.wordpress.com/2016/11/08/upload-dataset-onazure-sql-database-using-azure-event-hub-and-azure-stream-analytics/
https://pellitterisbiztalkblog.wordpress.com/2016/11/14/upload-flat-file-onazure-sql-database-using-azure-logic-app/
November 26°, 2016
#sqlsatParma
#sqlsat566
Q&A
Domande ?
November 26°, 2016
#sqlsatParma
#sqlsat566
#sqlsatParma
#sqlsat566
GRAZIE !
November 26°, 2016
#sqlsatParma
#sqlsat566