SQL Server, SQL Queries, DB concepts, Azure, Spark SQL, Tips & Tricks with >500 articles !!!
On 19th November 2024 Microsoft announced the Private Preview of SQL Server upcoming version i.e. SQL Server 2025 !!! This v-Next version emphasizes modern AI and Generative AI (GenAI) capabilities powered by Microsoft Copilot and contemporary Large Language Models (LLMs). These capabilities align with the latest advancements in GenAI, such as OpenAI’s ChatGPT, Meta’s […]
The SQL Server and Azure SQL team is revolutionizing AI app development with the Public Preview of SQL Database in Fabric which was announced at the Microsoft Ignite conference. Try it for free today! With SQL Database in Fabric, you can: Key Highlights: Learning: We have multiple resources to help you, […]
I just finished a course about “What Is Generative AI?” on LinkedIn. Here are some of my Key Takeaways: 1. Role of Generative AI: Generative AI is described as a tool that is changing how humans create. Its designed to generate new content or data that is similar to existing patterns. It’s used for tasks such as […]
SQL Planner is a Microsoft SQL Server monitoring Software product that helps DBA or Developer to identify issues (for ex. High CPU, Memory, Disk latency, Expensive query, Waits, Storage shortage, etc) and root cause analysis with a fast and deep level of analytical reports. Historical data is stored in the repository database for as […]
Happy New Year 2022… from SQL with Manoj !!! As WordPress.com Stats helper monkeys have stopped preparing annual report from last few years for the blogs hosted on their platform. So I started preparing my own Annual Report every end of the year to thank my readers for their support, feedback & motivation, […]
SQL Server 2022 is coming !!! On 2nd November 2021 in MS Ignite 2021 event Microsoft announced the preview of new version of SQL Server i.e. SQL Server 2022. SQL Server 2022 will be more focused on Azure-enabled cloud and big data space, here are some excerpts from the announcements: […]
I got an email from one SQL Server developer that he is not able to use import/export wizard and it is failing with below error: TITLE: SQL Server Import and Export Wizard -------------------------------------- Data flow execution failed. Error 0xc0000033: {5CCE2348-8B9F-4FD0-9AFA-9EA6D19576A7}: Integration Services evaluation period has expired. Error 0xc0000033:…
In SQL when you are syncing a table (target) from an another table (source) you need to make sure there are no duplicates or repeated datasets in either of the Source or Target tables, otherwise you get following error: UnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same […]
As you start using Python you will fall in love with it, as its very easy to solve problems by writing complex logic in very simple, short and quick way. Here we will see how to remove rows from a DataFrame based on an invalid List of items. Let’s create a sample Pandas […]
As I’m new to Python and these days using it for some Data Analysis & Metadata handling purpose, and also being from SQL background here I’m trying to use as many analysis features I use with SQL, like Group By, Aggregate functions, Filtering, Pivot, etc. Now I had this particular requirement to PIVOT […]
In one of my [previous post] we saw how to retrieve all attributes from the items (JSON document) of all Collections under all Databases by using C# .net code. Here in this post we will see how we can retrieve the same information in Azure Databricks environment by using Python language instead of C# […]
In one of my [previous post] we saw how to connect to Cosmos DB from Databricks by using the Apache Spark to Azure Cosmos DB connector. But that connector is limited to read and write data in Cosmos DB from Databricks compute using Scala language. Here in this post we will see how can […]
Data typecasting errors are common when you are working with different DataFrames across different languages, like here in this case I got datatype mixing error between Pandas & Spark dataframe: TypeError: field col1: Can not merge type <class 'pyspark.sql.types.longtype'> and <class 'pyspark.sql.types.stringtype'> While converting the Pandas DataFrame to Spark DataFrame its throwing error […]
In this post we will using Databricks compute environment to connect to Cosmos DB and read data by using Apache Spark to Azure Cosmos DB connector. First go to your Azure Databricks cluster and import the Azure Cosmos DB connector library. Download the library JAR from either [Maven links] or the [Uber JAR] […]
In our [previous post] we saw how to get COUNT of items/records from all Collections in all Databases of a CosmosDB instance by programmatically/dynamically iterating over all these objects. Here in this post we will extend it and see how to retrieve all attributes/fields from the JSON document/items present in these Collections. (This […]
Here in this post we will use C# .net code (for beginners like me) to see how to: 1. Connect to a Cosmos DB instance 2. Get list of all Databases in a Cosmos DB 3. Iterate through all the Databases and get the list of all Collections (or Tables) 4. Get COUNT of […]
Here in this post we will try to access VSO Work Items from Postman tool by using Azure DevOps REST APIs. To access and authenticate into Azure DevOps we will first create PAT (Personal Access Token) and then use it in the Postman tool. Create Token in Azure DevOps portal: 1. Go […]
I was trying to use the Azure DevOps REST APIs to get some details of VSO WorkItems by using Postman tool. Doing GET-Request was not a problem and it returned the expected JSON response body with all attributes and details. But when I tried to update the same VSO WorkItem by using POST request […]
Backup to Azure was made available with SQL Server 2012 SP1 CU2. It provided significant cost savings versus on-premises costs of onsite/offsite storage, and device maintenance and better scalability than logical drives connected to Azure machines. But backup to Azure was comparatively slow, and the maximum backup size was 1 TB, till SQL Server […]
Happy New Year 2021… from SQL with Manoj !!! As WordPress.com Stats helper monkeys have stopped preparing annual report from last few years for the blogs hosted on their platform. So I started preparing my own Annual Report every end of the year to thank my readers for their support, feedback & motivation, and […]
With Spark 3.0 release (on June 2020) there are some major improvements over the previous releases, some of the main and exciting features for Spark SQL & Scala developers are AQE (Adaptive Query Execution), Dynamic Partition Pruning and other performance optimization and enhancements. Below I’ve listed out these new features and enhancements all together […]
image credits: Databricks RDD (Resilient Distributed Dataset) Spark works on the concept of RDDs i.e. “Resilient Distributed Dataset”. It is an Immutable, Fault Tolerant collection of objects partitioned across several nodes. With the concept of lineage RDDs can rebuild a lost partition in case of any node failure. – In Spark initial versions […]
1. Spark Driver: – The Driver program can run various operations in parallel on a Spark cluster. – It is responsible to communicate with the Cluster Manager for allocation of resources for launching Spark Executors. – And in parallel it instantiates SparkSession for the Spark Application. – The Driver program splits the Spark Application […]
Apache Spark is an open source, unified analytics engine, designed for distributed big data processing and machine learning. Although Apache Hadoop was still there to cater for Big Data workloads, but its Map-Reduce (MR) framework had some inefficiencies and was hard to manage & administer. – A typical Hadoop program involves multiple iterative MR […]
I was creating a Hive table in Databricks Notebook from a Parquet file located in Azure Data Lake store by following command: But I was getting following error: warning: there was one feature warning; re-run with -feature for details java.lang.UnsupportedOperationException: Parquet does not support decimal. See HIVE-6384 As per the above error it […]
I was setting up an Azure Data Factory (ADF) to copy files from Azure Data Lake Storage Gen1 to Gen2, but while running the Pipeline it was failing with below error: Operation on target Copy_sae failed: Failure happened on ‘Sink’ side. ErrorCode=DelimitedTextMoreColumnsThanDefined, ‘Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException, Message=Error found when processing ‘Csv/Tsv…
There are times when you are left with no or very less space in you SQL Server VM disks, and the main cause of this is not only the lot amount of data coming in but the heavy processing your database Server is doing, and thus filling up the entire log space. SQL […]
So while creating a Python notebook and running it on my Databricks Cluster I observed following error: Your administrator has only allowed sql and scala commands on this cluster. This execution contained at least one disallowed language. Its obvious that the error is due to some restriction applied at Cluster level. So I […]
While working on some data analysis I saw one Spark SQL query was not getting me expected results. The table had some good amount of data, I was filtering on a value but some records were missing. So, I checked online and found that Spark SQL works differently compared to SQL Server, in this […]
In SQL Server to get top-n rows from a table or dataset you just have to use “SELECT TOP” clause by specifying the number of rows you want to return, like in the below query. But when I tried to use the same query in Spark SQL I got a syntax error, which meant […]
Today on 4th November 2019 Microsoft in MSIgnite2019 event announced the release of new version of SQL Server i.e. SQL Server 2019. New stuff in SQL Server 2019 is all about Big Data Clusters for SQL Server, which will allow you to: – Deploy scalable clusters of SQL Server, Spark, HDFS on Kubernetes […]
The following JSON contains some attributes at root level, like ProductNum and unitCount. It also contains a Nested attribute with name “Properties”, which contains an array of Key-Value pairs. Now, what I want is to expand this JSON, and have all the attributes in form of columns, with additional columns for all the Keys […]
Happy New Year 2019… from SQL with Manoj !!! As WordPress.com stats helper monkeys have stopped preparing annual report for any of their blogs, so I’ve prepared my own Annual Report for this year again. –> Here are some Crunchy numbers from 2018 The Louvre Museum has 8.5 million visitors per year. This […]
HDFS or Hadoop Distributed File System is the distributed file system provided by the Hadoop Big Data platform. The primary objective of HDFS is to store data reliably even in the presence of node failures in the cluster. This is facilitated with the help of data replication across different racks in the cluster infrastructure. […]
1. Open RUN by pressing Windows + R keys, type powershell command and hit Enter. 2. Now on Powershell window first connect to the Azure VM that you want to remotely restart: [MyAzureVMName]: PS C:\Users\manojp\Documents> 3. Now try issuing the Restart command: Restart-Computer : Failed to restart the computer MyAzureVMName with the […]
Today on 24th September 2018 Microsoft announced the released of first CTP version of SQL Server vNext i.e. SQL Server 2019 CTP 2.0. SQL Server 2016 & 2017 brought Machine Learning feature by adding R and Python support for Data Analytics and Scientists. And now SQL Server 2019 has added Big Data capabilities […]
Microsoft SQL Server is the most popular database management system, which provides flexibility to the database administrator to manage their database. It is a full-featured database and designed for use in corporate applications. But, many times, many users need to migrate their SQL Server database to the Azure SQL Database. Because Azure is an […]
Setup SQL Server on Linux: 1. Spinning up a new Linux VM on Microsoft Azure 2. Install and Configure SQL Server 2017 on Linux Azure VM 3. Connect SQL Server on Linux with SSMS from a Windows machine Best Practices: Here are some of the best practices post installing SQL Server on Linux […]
You are running a Python script by using sp_execute_external_script SP but its throwing this error: Msg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 27] ‘sp_execute_external_script’ is disabled on this instance of SQL Server. Use sp_configure ‘external scripts enabled’ to enable it. You can refer to my blog […]
With SQL Server 2016 Microsoft added Machine Learning support with R Language in SQL engine itself and called it SQL Server R Services. Going ahead with the new SQL Server 2017 version Microsoft added Python too as part of Machine Learning with existing R Language, and thus renamed it to SQL Server Machine Learning […]
Databricks Introduction – What is Azure Databricks [Video] – Create Databricks workspace with Apache Spark cluster – Extract, Transform & Load (ETL) with Databricks – Documentation: – Azure – Databricks From Channel 9 1. Data Science using Azure Databricks and Apache Spark [Video] 2. Data ingestion, stream processing and sentiment analysis using Twitter […]
Databricks Introduction: Azure Databricks = Best of Databricks + Best of Azure Azure Databricks is an Apache Spark-based analytics platform optimized for the Microsoft Azure cloud services platform (PaaS). It is a fast, easy-to-use, and collaborative Apache Spark–based analytics platform. Designed in collaboration with the creators of Apache Spark, it combines the best of […]
SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances. Internal Mechanism & Performance impact: Data Sync uses insert, update, and delete triggers to track changes. It creates side tables in the user database for […]
Blockchain is a disruptive technology that is going to transform many industries in near future. Bitcoin is one of its implementation. Let’s check what is Blockchain is all about. What is Blockchain? A Blockchain is an immutable, secure, digital, distributed ledger without a central authority, which uses public/private signature technology to validate and […]
I observed one thing here with BCP (Bulk Copy Program), when you have 2 versions of SQL Server installed on you PC or Server. I had SQL Server 2014 & 2016 installed on one of my DEV server. So if you are executing Query from SQL 2016 instance, it was inserting records in […]
Yesterday my friend pinged me and told that he is facing some issues while executing a SQL Job DDL script. They had upgraded their SQL Server version from 2008 to 2016, and while creating SQL Jobs they were facing below error: Msg 14234, Level 16, State 1, Procedure sp_verify_subsystem, Line 28 [Batch Start Line […]
Today while installing SQL Server 2017 on my PC I got this popup on the Installation Progress tab. Earlier I had installed SQL Server 2017 but had to uninstall it for some reason. Now today while re-installing it I was getting this error. TITLE: Microsoft SQL Server 2017 Setup —————————— The following error has […]
In my [previous post] I’ve tried to collate some basic stuff about HDInsight to let you know the basics and get started. You can also check [Microsoft Docs] for HDInsight to know more and deep dive into the Big-Data platform. Microsoft Certification Exams is one of a good and easy approach to understand […]
The Microsoft Azure portal has all the details on HDInsight and is very vast. Here in this post I’ve simply curated main and important stuff for myself and others to get started with HDInsight. Azure HDInsight is a standard Apache Hadoop distribution offered as a managed service on Microsoft Azure. It is based […]
With SQL Server 2014 Microsoft made its SQL Server Developer Edition free for Development and Test database in a non-production environment. This edition is not meant for Production environments or for use with production data. With SQL Server 2017 Developer edition developers can build any kind of application on top of SQL Server. It […]