Upload data on Azure SQL Database using Azure

Upload data on Azure SQL Database using Azure Messaging, Stream Analytics & Logic Apps Salvatore Pellitteri @pellittsa https: //pellitterisbiztalkblog. wordpress. com/ November 26°, 2016 #sqlsat. Parma #sqlsat 566

Sponsors November 26°, 2016 #sqlsat. Parma #sqlsat 566

Organizers getlatestversion. it November 26°, 2016 #sqlsat. Parma #sqlsat 566

Salvatore Pellitteri | @pellittsa • Developer Team Manager in Microsys Srl. • Microsoft MVP Azure – Application Integration • Microsoft P-Seller competenza Data Platform • SQL / BI / Integration Architect. November 26°, 2016 #sqlsat. Parma #sqlsat 566

Agenda • Introduzione • Upload dataset su SQL Database – Event Hubs – Stream Analytics • Upload file su SQL Database – Service Bus – Logic Apps November 26°, 2016 #sqlsat. Parma #sqlsat 566

Introduzione November 26°, 2016 #sqlsat. Parma #sqlsat 566

Introduzione Strumenti a disposizione – SQL Server Integration Services – BCP – Bulk Copy API – … Qual’è il focus di questa presentazione ? - “Consideriamo soluzioni alternative” November 26°, 2016 #sqlsat. Parma #sqlsat 566

Introduzione • Quali sono gli scenari ? – IOT – Application Integration – B 2 B Integration – … – In generale piccoli pacchetti di dati ma frequenti November 26°, 2016 #sqlsat. Parma #sqlsat 566

Introduzione • Quali sono le sfide ? – Upload dataset di sorgenti dati eterogenee e distribuite geograficamente – Upload di flat file con schemi complessi November 26°, 2016 #sqlsat. Parma #sqlsat 566

UPLOAD DATASETS SU SQL DATABASE November 26°, 2016 #sqlsat. Parma #sqlsat 566

Layers On-Prem Custom Code November 26°, 2016 Az Event Hubs Az Stream Analytics Az SQL Database #sqlsat. Parma #sqlsat 566

Azure Event Hubs November 26°, 2016 #sqlsat. Parma #sqlsat 566

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 #sqlsat. Parma #sqlsat 566

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 #sqlsat. Parma #sqlsat 566

Azure Event Hubs § Partizioni Event Hub Partition 1 Partition 2 Partition N November 26°, 2016 #sqlsat. Parma #sqlsat 566

Sviluppare un event publisher 1. Installare il Nu. Get Package 2. Scrivere il codice using System. Threading; using Microsoft. Service. Bus. Messaging; . . . string connection. String = "{Connection String}"; string event. Hub. Name = "{Event Hub Name}"; var event. Hub. Client = Event. Hub. Client. Create. From. Connection. String(connection. String, event. Hub. Name); event. Hub. Client. Send(new Event. Data(Encoding. UTF 8. Get. Bytes({Data}))); // Max 256 KB event. Hub. Client. Close(); https: //azure. microsoft. com/en-us/documentation/articles/event-hubs-csharp-ephcs-getstarted/ November 26°, 2016 #sqlsat. Parma #sqlsat 566

Azure Stream Analytics «Azure Stream Analytics consente di sviluppare e distribuire rapidamente soluzioni a basso costo per ottenere informazioni approfondite in tempo reale da dispositivi, sensori, infrastruttura e applicazioni» November 26°, 2016 #sqlsat. Parma #sqlsat 566

Azure Stream Analytics Event Consumer - Event Hub - Io. T Hub - Blob Storage November 26°, 2016 #sqlsat. Parma #sqlsat 566

Azure Stream Analytics Input • Event Hub • Io. T Hub • Blob Storage November 26°, 2016 Query <> Output • • SQL Database Blob Storage Event Hub Table Storage Service Bus * Document. DB Power BI Data Lake Store #sqlsat. Parma #sqlsat 566

Check List Resource Group Event Hubs Namespace Event Hubs queue Stream Analytics Job Input Job Output Job Query November 26°, 2016 #sqlsat. Parma #sqlsat 566

DEMO Upload Dataset su Azure SQL Database November 26°, 2016 #sqlsat. Parma #sqlsat 566

UPLOAD FLAT FILE SU SQL DATABASE November 26°, 2016 #sqlsat. Parma #sqlsat 566

Introduzione November 26°, 2016 #sqlsat. Parma #sqlsat 566

Layers On-Prem Custom Code November 26°, 2016 Az Service Bus Az Logic Apps Az SQL Database #sqlsat. Parma #sqlsat 566

Azure Service Bus Una coda con un meccanismo di publish-and-suscribe November 26°, 2016 #sqlsat. Parma #sqlsat 566

Azure Service Bus - Queue • Comunicazione unidirezionale • Ogni coda funge da intermediario (AKA broker) che archivia i messaggi inviati fino a quando non vengono ricevuti • Ogni messaggio viene ricevuto da un singolo destinatario Message Publisher November 26°, 2016 Queue Message Consumer #sqlsat. Parma #sqlsat 566

Sviluppare un event publisher 1. Installare il Nu. Get Package 2. Scrivere il codice ? a os u Q a d r o c Ri c l a using Microsoft. Service. Bus. Messaging; . . . string connection. String = "{Connection String}"; string queue. Name = "{Queue Name}"; var queue. Client = Queue. Client. Create. From. Connection. String (connection. String, queue. Name); // Max 256 KB Standard Subscription – Max 1 MB Premium Subscription queue. Client. Send(new Brokered. Message({data})); queue. Client. Close(); https: //azure. microsoft. com/en-us/documentation/articles/service-bus-dotnet-get-started-with-queues/ November 26°, 2016 #sqlsat. Parma #sqlsat 566

Azure Logic Apps «Logic Apps provide a way to simplify and implement scalable integrations and workflows in the cloud» November 26°, 2016 #sqlsat. Parma #sqlsat 566

Azure Logic Apps November 26°, 2016 #sqlsat. Parma #sqlsat 566

Azure Logic Apps • Concetti – Workflow – Trigger – Actions – Managed Connectors – Enterprise Integration Pack November 26°, 2016 #sqlsat. Parma #sqlsat 566

Check List Resource Group Service Bus Namespace Service Bus queue API connection to SQL database Storage Account App Service Plan Function App Integration Account Upload flat file schema Upload sql procedure parameter schema Upload transformation Logic App November 26°, 2016 #sqlsat. Parma #sqlsat 566

DEMO Upload Flat File su Azure SQL Database November 26°, 2016 #sqlsat. Parma #sqlsat 566

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/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 #sqlsat. Parma #sqlsat 566

Q&A Domande ? November 26°, 2016 #sqlsat. Parma #sqlsat 566

#sqlsat. Parma #sqlsat 566 GRAZIE ! November 26°, 2016 #sqlsat. Parma #sqlsat 566