The MERGE statement was introduced in SQL Server 2008 and the developers embraced it immediately. Later a feedback came and the statement was criticized as not very efficient. Aaron Bertrand collected some links about this here. MERGE statement anatomy The MERGE statement joins ‘source’ and ‘target’ tables and runs INSERT, […]
Set-based Database Development As database developer, we need to switch our mindsets to “Set-based thinking”. In simple words we need to manipulate the data as a “set”. An example of set-based database development is: JOIN tables Aggregate the result Calculate the portion (%) of a row to the total of […]
In a few of my previous posts (links at the end of this post), i created Kafka consumer with Python. The same can be done with Azure Function. in this example i show how to pass secrets to the Azure Function with Azure Key Vault. Create Key Vault ‘kv-afkv-test’ in […]
Download JupyterLab portable from portabledevapps.net: Install and start the app: Open in browser: Add PyPI: Add the code that i posted here and start the development: Related posts: Python: Build JSON Array and keep the last object based on key Keep it simple :-)
An ETL that i build recently instigated me to share the following excerpt of python code. The players in this ETL are: Apache Kafka (Source) Azure Data Factory (ETL app) Azure Databricks (Extract and Transform with Python) Azure Data Lake Storage (File storage) Cosmos DB (Destination) In this example i […]
Let say that we need the following transformation in SSRS report: One of the tools to do this is List. I will explain only the tricky part and will give the .rdl in the end. Drag-and-drop List in the report body: Right click the top left corner of the list […]
XML is dead, long live JSON! Even before i knew JSON, i thought “XML contains more metadata than data”. Today in my new development, i prefer JSON and avoid using XML, but in some cases we need to fight with XML too. In this quick example i show how to […]
This python code can be used to extract two files from Kafka in Azure Datalake (ADLS): extract/kafka/topic/topic_{YYYYMMDD_HHMMSS}.json – no duplicates (PK: parentId|id) extract/kafka/topic_history/topic_{YYYYMMDD_HHMMSS}.json – all the rows (PK: parentId|id|date_created) If case of error, the KafkaException is exported in a file with name error_topic_{YYYYMMDD_HHMMSS}.txt. ADF determines if there is an error, […]
Entity-Atribute-Value (EAV) data model gives us the flexibility to store the data in a way that we have dynamic number of: tables columns in the simplest EAV we have 3 tables: Entity Attribute Value To simplify the understanding of the model we declare: Table Entity holds the list of the […]
In this example we’ll create SQL Server Common Language Runtime (CLR) to read/write from/to REST API. Technology used: Visual Studio 2019 (C# Windows .NET Class Library) SQL Server 2019 (on premises) Authentication: Basic (base64 encoded Username:Password) Requests: HTTP SQL GET SELECT POST INSERT Visual Studio 2019 Create new C#, Windows, […]