1)
Azure Databricks is a
powerful platform that is built on top of Apache Spark and is designed
specifically for huge data analytics. Setting it up and deploying it to Azure
take just a few minutes, and once it's there, using it is quite easy. Because
of its seamless connectivity with other Azure services, Databricks is an
excellent choice for data engineers who want to deal with big amounts of data
in the cloud. This makes Databricks an excellent solution.
2)
DBU stands for Databricks Unified, which is a Databricks
framework for handling resources and calculating prices.
3)
Azure Databricks comes with many benefits including reduced
costs, increased productivity, and increased security.
4)
Azure Databricks has four types of clusters, including
Interactive, Job, Low-priority, and High-priority.
5)
Caching: The cache refers to the practice of storing
information temporarily. When you go to a website that you visit frequently,
your browser takes the information from the cache instead of the server. This
helps save time and reduce the server’s load.
6)
Autoscaling is a Databricks feature that will help you
automatically scale your cluster in whichever direction you need.
7)
KAFKA: When Azure Databricks gathers data, it establishes
connections to hubs and data sources like Kafka. Apache Kafka is an open-source
distributed event streaming platform used by thousands of companies for
high-performance data pipelines, streaming analytics, data integration, and
mission-critical applications.
8) DBFS: The Databricks
filesystem is used to store the data that is saved in Databricks. Workloads
involving large amounts of data are an ideal fit for this particular
distributed file system. The Hadoop Distributed File System (DVFS) is
compatible with Databricks, which is a distributed file system (HDFS).
9) What is the
difference between an instance and a cluster in Databricks? An instance is a
virtual machine that helps run the Databricks runtime. A cluster is a group of
instances that are used to run Spark applications.
10)
Management Plan: The management plan is how you manage
and monitor your Databricks deployment.
11)
Control Plan: The control plane is responsible for
managing Spark applications.
12)
Data Plan: The data plane is responsible for storing and
processing data.
13)
The Databricks runtime is often used to execute the
Databricks platform’s collection of modules.
14)
Databricks Secret: A secret is a key-value combination that
can help keep secret content; it is composed of a unique key name contained
within a secret context. Each scope is limited to 1000 secrets. It cannot
exceed 128 KB in size.
15)
Any information that is
stored in the Databricks Delta format is stored in a table that is referred to
as a delta table. Delta tables, in addition to being fully compliant with ACID
transactions, also make it possible for reads and writes to take place at
lightning speed.
16)
An application environment
that is created on top of Apache Spark is referred to as the Databricks
Runtime. It provides everything you need to construct and run Spark applications,
such as libraries, application programming interfaces (APIs), and tools
17)
Dataframe: A data frame is a particular form of table that is used for
the storage of data within the Databricks runtime. There is complete support
for ACID transactions, and data frames were developed with the goal of
providing fast reads and writes.
The Dataset is an extension of the Dataframe with more added features
like type-safety and object-oriented interface. The
Dataframes is defined as the distributed collection organized into named
columns.
18)
Temporary
views in Spark SQL are session-scoped and will disappear if the session that
creates it terminates. If you want to have a temporary view that is shared
among all sessions and keep alive until the Spark application terminates, you
can create a global temporary view. Global temporary view is tied to a system
preserved database global_temp, and we must use the qualified name to refer it, e.g. SELECT * FROM global_temp.view1.
Azure Synapse Analytics.
It was developed specifically to manage tables with hundreds of millions
of rows. Because it is based on a Massively Parallel Processing, or MPP,
architecture, Synapse SQL is able to conduct complicated queries and provide
the query answers within seconds, even when working with large amounts of data.
This is made possible by the fact that Azure Synapse Analytics can distribute
data processing across numerous nodes.
19)
The Dedicated SQL Pool of
Azure Synapse Analytics is a collection of technologies that enables you to
leverage the platform that is typically utilized for enterprise data
warehousing. The provisioning of the resources in the Data Warehousing Units is
accomplished with the help of Synapse SQL (DWU). A dedicated SQL pool improves
the efficiency of queries and decreases the amount of data storage that is
required by storing information in both columnar and relational tables.
|
Star
Schema
|
Snowflake
Schema
|
|
Hierarchies
for the dimensions are stored in the dimensional table.
|
Hierarchies
are divided into separate tables.
|
|
It
contains a fact table surrounded by dimension tables.
|
One
fact table surrounded by dimension table which are in turn surrounded by
dimension table
|
|
In a
star schema, only single join creates the relationship between the fact table
and any dimension tables.
|
A
snowflake schema requires many joins to fetch the data.
|
|
Simple
DB Design.
|
Very
Complex DB Design.
|
|
Denormalized
Data structure and query also run faster.
|
Normalized
Data Structure.
|
|
High
level of Data redundancy
|
Very low-level
data redundancy
|
|
Single
Dimension table contains aggregated data.
|
Data
Split into different Dimension Tables.
|
|
Cube
processing is faster.
|
Cube
processing might be slow because of the complex join.
|
|
Offers
higher performing queries using Star Join Query Optimization.
Tables may be connected with multiple dimensions.
|
The
Snowflake schema is represented by centralized fact table which unlikely
connected
with multiple dimensions.
|
20)
OLAP: An OLAP system is designed to process large amounts of data
quickly, allowing users to analyze multiple data dimensions in tandem. Teams
can use this data for decision-making and problem-solving. OLAP’s
multidimensional schema is well suited for complex queries that draw from
multiple data sets, such as historical and current data, including from OLTP
sources as mentioned. OLAP systems are designed to process queries that include
thousands to millions of rows of data. Data is updated hourly to daily
depending on the needs of the organization. OLAP databases process
significantly more data, so their response times are slower. OLAP systems
require massive amounts of data storage capacity to function
Organizations
use OLTP systems to run their business while OLAP systems help them understand
their business.
21)
OLTP: OLTP systems are designed to handle large volumes of transactional
data involving multiple users. Relational databases rapidly update, insert, or
delete small amounts of data in real time. Most OLTP systems are used for
executing transactions such as online hotel bookings, mobile banking
transactions, ecommerce purchases, and in-store checkout. Many OLAP systems
pull their data from OLTP databases via an ETL pipeline and can provide insights such as analyzing ATM
activity and performance over time. An OLTP system stores transaction data
in a relational database, optimized to handle the large volumes of
transactional data funneled into this system. OLTP systems typically
update a few rows of data at a time in real time or near real time. OLTP
systems are also backed up much more frequently than OLAP systems—due to OLTP’s
nature as a transaction processing tool, regular backups are required to
maintain business operations and comply with relevant legal and regulatory
requirements. OLTP systems have response times that are measured in
milliseconds. OLTP systems have relatively modest data storage requirements
What is Azure Data Factory?
Cloud-based integration service that allows
creating data-driven workflows in the cloud for orchestrating and automating
data movement and data transformation.
·
Using Azure data
factory, you can create and schedule the data-driven workflows(called
pipelines) that can ingest data from disparate data stores.
·
It can process and
transform the data by using compute services such as HDInsight Hadoop, Spark,
Azure Data Lake Analytics, and Azure Machine Learning.
MapReduce is a
programming paradigm that enables massive scalability across hundreds or
thousands of servers in a Hadoop cluster. As the processing component,
MapReduce is the heart of Apache Hadoop. The term "MapReduce" refers
to two separate and distinct tasks that Hadoop programs perform.
22) The integration runtime is the compute
infrastructure that Azure Data Factory uses to provide the following data
integration capabilities across various network environments.
a)
Self Hosted IR
b)
Azure IR
c)
Azure-SSIS IR
- Datasets: Sources of data. In simple words, it is a data
structure that holds our data.
- Linked services: These store information that is very important when
it comes to connecting an external source.
23)
Val can’t be reassigned whereas Var can be
reassigned.
How to Handle Corrupt Records
in Databricks:
Creation DataFrame using DROPMALFORMED mode
Here w
e are creating the dataframe by
reading the file. By using the schema() function,
we can define our schema while reading the file's contents. Here we are
using DROPMALFORMED mode
while reading the file. This mode populates only cleaned records without
throwing any error, and the corrupted records are discarded during the creation
of the dataframe.
The file contains only five
records, out of which four are cleaned, and one is corrupted. In the below
result set, if you observe, the dataframe contains only four cleaned records,
and the bad record is not populated.
val empDF = spark.read
.schema("id Integer,name
String,doj Date,salary Integer")
.option("header",true)
.option("mode",
"DROPMALFORMED") FailFast”
.option("dateformat","dd.MM.yyyy")
.csv("/FileStore/tables/empdata.txt")
display(empDF)
val empDF
= spark.read
.schema("id Integer,name String,doj
Date,salary Integer,_corrupt_record String")
.option("header",true)
.option("mode",
"PERMISSIVE")
.option("columnNameOfCorruptRecord",
"_corrupt_record")
.option("dateformat","dd.MM.yyyy")
.csv("/FileStore/tables/empdata.txt")
display(empDF)
Step 4: Filter Clean Records
For cleaned records the
"_corrupt_record" column populates "null" values. Here we
are filtering only cleaned records by keeping col("_corrupt_record").isNullcondition
in filter function and there by dropping "_corrupt_record".
import org.apache.spark.sql.functions._
//Filter the cleaned records
empDF.filter(col("_corrupt_record").isNull).drop(col("_corrupt_record")).show()
ADLS VS BLOB STORAGE:
Structure
Blob: Flat namespace
object store.
ADLS: Hierarchical
namespaces (much like a File
System).
Purpose
Blob: General purpose
object store for a wide variety of storage scenarios, including big data
analytics.
ADLS: Optimized
storage for big data analytics workloads.
Performance (Analytics
Workload)
Blob: Good storage
retrieval performance.
ADLS: Better storage
retrieval performance.
Cost
Blob: High cost for
Analysis.
ADLS: Low cost for
Analysis.
Hierarchical
namespaces organize blob data
into directories and stores metadata about each directory and the files within
it. They keep the data organized, which yields better storage and retrieval performance
for an analytical use case and lowers the cost of analysis. This structure
allows operations, such as directory renames and deletes, to be performed in a
single atomic operation.
Flat namespaces,
by contrast, require several operations proportionate to the number of objects
in the structure.
PYSPARK
SERIALIZATION:
Serialization is used for performance tuning on Apache Spark.
All data that is sent over the network or written to the disk or persisted in
the memory should be serialized. Serialization plays an important role in
costly operations.
MarshalSerializer
Serializes
objects using Python’s Marshal Serializer. This serializer is faster than
PickleSerializer, but supports fewer datatypes.
class pyspark.MarshalSerializer
PickleSerializer
Serializes
objects using Python’s Pickle Serializer. This serializer supports nearly any
Python object, but may not be as fast as more specialized serializers.
PySpark - SparkContext
SparkContext
is the entry point to any spark functionality. When we run any Spark
application, a driver program starts, which has the main function and your
SparkContext gets initiated here. The driver program then runs the operations
inside the executors on worker nodes.
SparkContext
uses Py4J to launch a JVM and creates a JavaSparkContext.
By default, PySpark has SparkContext available as ‘sc’, so creating
a new SparkContext won't work.
class pyspark.SparkContext (
master = None,
appName = None,
sparkHome = None,
pyFiles = None,
environment = None,
batchSize = 0,
serializer = PickleSerializer(),
conf = None,
gateway = None,
jsc = None,
profiler_cls = <class 'pyspark.profiler.BasicProfiler'>
)
·
Master − It is the URL of the cluster it
connects to.
·
appName − Name of your job.
·
sparkHome − Spark installation directory.
·
pyFiles − The .zip or .py files to send to the
cluster and add to the PYTHONPATH.
·
Environment − Worker nodes environment variables.
·
batchSize − The number of Python objects
represented as a single Java object. Set 1 to disable batching, 0 to
automatically choose the batch size based on object sizes, or -1 to use an
unlimited batch size.
·
Serializer − RDD serializer.
·
Conf − An object of L{SparkConf} to set all
the Spark properties.
·
Gateway − Use an existing gateway and JVM,
otherwise initializing a new JVM.
·
JSC − The JavaSparkContext instance.
·
profiler_cls − A class of custom Profiler used to do
profiling (the default is pyspark.profiler.BasicProfiler).
RDD stands for Resilient Distributed
Dataset, these are the elements that run and operate on multiple nodes to
do parallel processing on a cluster. RDDs are immutable elements, which means
once you create an RDD you cannot change it. RDDs are fault tolerant as well,
hence in case of any failure, they recover automatically. You can apply
multiple operations on these RDDs to achieve a certain task.
To apply operations on these RDD's, there are
two ways −
·
Transformation and
·
Action
For parallel processing, Apache Spark uses
shared variables. A copy of shared variable goes on each node of the cluster
when the driver sends a task to the executor on the cluster, so that it can be
used for performing tasks.
There are two types of shared variables
supported by Apache Spark −
·
Broadcast: Broadcast variables are used to save the copy of data across
all nodes. This variable is cached on all the machines and not sent on machines
with tasks.
·
·
Accumulator: Accumulator variables are used for aggregating the
information through associative and commutative operations.
·
PYSPARK CONF
To
run a Spark application on the local/cluster, you need to set a few configurations
and parameters, this is what SparkConf helps with. It provides configurations
to run a Spark application. The following code block has the details of a
SparkConf class for PySpark.
from pyspark import SparkConf, SparkContext
conf = SparkConf().setAppName("PySpark App").setMaster("spark://master:7077")sc = SparkContext(conf=conf)
PYSPARK SPARK FILES:
In Apache Spark, you can upload your files
using sc.addFile (sc is your default SparkContext) and get the
path on a worker using SparkFiles.get. Thus, SparkFiles resolve the
paths to files added through SparkContext.addFile().
SparkFiles contain the following classmethods −
·
get(filename)
·
getrootdirectory()
PYSPARK STORAGE LEVEL:
StorageLevel
decides how RDD should be stored. In Apache Spark, StorageLevel decides whether
RDD should be stored in the memory or should it be stored over the disk, or
both. It also decides whether to serialize RDD and whether to replicate RDD
partitions.
PYTHON LAMBDA
A lambda function is a small anonymous function. A lambda
function can take any number of arguments, but can only have one expression.
x = lambda a : a + 10
print(x(5))
The power of lambda is better shown when you use them as an
anonymous function inside another function.
Say you have a function definition that takes one argument, and
that argument will be multiplied with an unknown number:
def myfunc(n):
return lambda a : a * n
mydoubler = myfunc(2)
mytripler = myfunc(3)
print(mydoubler(11))
print(mytripler(11))
What
is Apache Parquet?
Apache Parquet is a file
format designed to support fast data processing for complex data, with several
notable characteristics:
Columnar: Unlike
row-based formats such as CSV or Avro, Apache Parquet is column-oriented –
meaning the values of each table column are stored next to each other, rather
than those of each record:
Open-source: Parquet
is free to use and open source under the Apache Hadoop license, and is
compatible with most Hadoop data processing frameworks.
Self-describing:
In addition to data, a Parquet file contains metadata including schema and
structure. Each file stores both the data and the standards used for accessing
each record – making it easier to decouple services that write, store, and read
Parquet files.
SQL SPLITTER:
DECLARE @start_position INT,
@ending_position INT
SELECT @start_position = 1,
@ending_position = CHARINDEX(@delimiter_character, @string_value)
WHILE @start_position < LEN(@string_value) + 1
BEGIN
IF @ending_position = 0
SET @ending_position = LEN(@string_value) + 1
INSERT INTO @result_set (splited_data)
VALUES(SUBSTRING(@string_value, @start_position, @ending_position - @start_position))
SET @start_position = @ending_position + 1
SET @ending_position = CHARINDEX(@delimiter_character, @string_value, @start_position)
Audit Log Table: https://www.youtube.com/watch?v=ZdeatJidrAo
Pivot_df = Explode_df.select.groupby(“operation”).pivot(“key”).value
Display(pivot_df)
From pyspark.sql import sparksession
Spark=Sparksession.builder.master(“local”).getorcreate()
Print(spark.sparkcontext.version)
Df1=spark.createdataFrame(data=employ_data,schema=employ_schema)
Narrow Transformation: Filter is NT(1:1 transformation)
Df2=df1.filter(employeedid=200)
Wide Transformation: Group by is WT(Many:1 Transformation
usually involves shuffle)
Df2=df1.groupby(“dept”).count()
Z-ordering is a technique to colocate
related information in the same set of files. This co-locality is automatically
used by Delta Lake on Databricks data-skipping algorithms. This behavior
dramatically reduces the amount of data that Delta Lake on Databricks needs to
read. To Z-order data, you specify the columns to order on in the ZORDER BY clause:
Copy
OPTIMIZE events
WHERE date >= current_timestamp() - INTERVAL 1 day
ZORDER BY (eventType)
Repartition: It can be used to increase or decrease the
partition. It always shuffle the partition before creating new partition. They
are used for performance optimization.
Coaelsce: It always reduce the partition. Coalesce is
more performant than Repartition. It creates uneven partition.
Sc.defaultparallelism created 8 partition by default
Df.rdd.getnumpartition()
Cache VS Persist:
The only difference between cache() and persist() is ,using
Cache technique we can save intermediate results in memory only when needed
while in Persist() we can save the intermediate results in 5 storage levels(MEMORY_ONLY,
MEMORY_AND_DISK, MEMORY_ONLY_SERIALIZATION, MEMORY_AND_DISK_SER, DISK_ONLY).
Import org.apache.spark.storage.StorageLevel
Var rdd1= rdd1.persist(StorageLevel.Memory_Only)
By default storage level for RDD is Memory_Only
Memory_Only_2: it will replicate partition into two cluster
nodes.
What is Catalyst optimizer. An optimizer that automatically
finds out the most efficient plan to execute data operations specified in the
user's program. It “translates” transformations used to build the Dataset
to physical plan of execution.
Df.createdataframe(data=simple_data, schema=columns)
NTILE(N)
SQL RANK function
We use the NTILE(N)
function to distribute the number of rows in the specified (N) number of
groups. Each row group gets its rank as per the specified condition. We need to
specify the value for the desired number of groups.
3rd Highest Salary
select distinct salary from employee a
where 3 >= (select count(distinct salary) from employee b where a.salary
<= b.salary) order by a.salary desc;
Why choose a tabular solution:
- easier for
understanding and creating the model;
- works quicker than
multidimensional cubes for queries based on columns;
- hardware, such as
disks, is not essential. However, tabular is a memory dependent solution,
and more memory will ensure better performance;
- more efficient data compression about one-tenth of
the size, whereas compressed multidimensional data takes up a third of the
size of the original database
Why choose a multidimensional solution:
- works better with
a large amount of data – when we are talking
about terabytes, it's better to go with the multidimensional database. If
your database requires more than five terabytes, multidimensional is the
only option.
- performs better in
terms of scalability
- some features, such as
aggregations or actions, are supported in the multidimensional model only
Clustered ColumnStore Index
The index can be created during the creation of the table.
Unlike row level indexes, columnstore indexes do not require the column names
in the indexes of the corresponding table. Clustered columnstore indexes
operate on the entire table which is at the data page level. In general
clustering is arranging data in a specific order, so when using row level
indexes, we will sort the rows of the indexed column or column(s) specified in
the particular index. But when it comes to a columnstore index, the clustering
is done on the column followed by each column.
With the same size of keys, The
nonclustered indexes need more space than clustered indexes.
Logging level in ADF Data flow:
Verbose: Shows the partition
info and rows O/p for every transformation
Basic: Not show the partition
info
None: Only shows the rows copy
from source to sink.
MapReduce is a programming
model and an associated implementation for processing and
generating big data sets
with a parallel, distributed algorithm on a cluster.
A MapReduce program is composed of a map procedure, which performs filtering and
sorting (such as sorting students by first name into queues, one queue for each
name), and a reduce method,
which performs a summary operation (such as counting the number of students in
each queue, yielding name frequencies). The "MapReduce System" (also
called "infrastructure" or "framework") orchestrates the processing
by marshalling the
distributed servers, running the various tasks in parallel, managing all
communications and data transfers between the various parts of the system, and
providing for redundancy and fault tolerance.
Read Excel File In Databricks:
Df=Spark.read.format(“com.crealytics.spark.excel”).option(“inferscema”,true).option(“header”,True).
Option(“dataAddress”,”Sheet3!”).load(“filepath”)
|
Relational Database
|
NoSQL Database
|
|
Data is stored in
tables
|
Data can be stored
as documents, graphs, key-value pairs, etc.
|
|
Vertically scalable
|
Horizontally
scalable
|
|
Predefined schema
|
No predefined
schema, hence easier to update
|
|
Supports powerful
query language
|
Supports simple
query language
|
|
Can handle data in
moderate volumes
|
Can handle data in
very high volumes
|
|
Has a centralised
structure
|
Has a decentralised
structure
|
|
Data can be written
from one or a few locations
|
Data can be written
from many locations
|
Azure Cosmos
DB
Cosmos Database (DB) is a globally distributed, low latency,
multi-model database for managing data at large scales. It is a cloud-based
NoSQL database offered as a PaaS (Platform as a Service) from Microsoft Azure.
It is a highly available, high throughput, reliable database and is often
called a serverless database. Cosmos database contains the Azure Document DB
and is available everywhere.
How does Azure Cosmos DB work?
If a website used by people all over the world writes its data
into a primary database in one location (non-multi-master mode), the people
near to the location will be able to retrieve the data faster than the rest of
the world due to network latency issues.
But Cosmos DB has multi-master support where the data can be
simultaneously written into different databases spread out globally. In this
way, the data is replicated onto the user’s nearest region so that it can be
accessed faster. But there can still be a difference of milliseconds between
the data being replicated and this affects the consistency.
Consistency indicates whether the data are in sync and are at
the same state at any given point in time. Cosmos DB offers multiple levels of
consistency with varying performances and availability.
Df2=df1.colaesce(1).write.mode(‘overwrite’).parquet(“/dbfs/filestore/”)
Default Partition Size: 128 Mb in spark(Spark creates 1
partition for every 128 mb of data size)
Spark.conf.get(“spark.sql.files.MaxPartitionBytes”)
Managed Table: Spark manages both metadata & Data.
Databricks take care of both metadata and Storage location and store in
account. Drop table remove both
Unmanaged table: Spark only manage Metadata not data.
Data is controlled by you. Drop table will only delete the Metadata not data.
Count IPS from the JSON list:
Import json
import pandas as pd
ip_dict2=dict()
for j in samplejson:
print (j)
temp_dict=json.loads(j[0])
print(temp_dict)
for ip in temp_dict[‘ips’]:
print(ip)
if ip in ip_dict2.keys():
ip_dict2[ip]+=1
else:
ip_dict2[ip]=1
print(pd.Dataframe(ip_Dict2.items(),column[‘ip’,’count’])
Sort By: It will do sorting at partition level. Less Efficient.
Sort is not going to do any shuffle. It is cost saving.
Order BY: It will do sorting on complete data
irrespective of partition. It will do shuffle.
Df.orderBy(‘country’,’salary’
Map Function is transformation function and it applies on
every element of rdd.
Map partition is a transformation function and it applies
on every partition of rdd.
RDD.mapPartition(f,preservePartitioning=false)—mainly we
use reformat
Rdd2=Df.rdd.map(lambda x: x[0]+’,’+x[1],x[2],x[3]*2)
Df2=rdd2.todf
Count Words in EBOOK:
Explode will put all the
words in a single row which is easier to read
Scenario2 : To check the data skuness and count the words
in particular partition using spark_partition_id
Types of Join Strategy in Spark:
1)
Broadcast Hash Join: When one of the data
frames is small and fits in the memory, it will be broadcasted to all the
executors, and a Hash Join will be performed.
2)
Shuffle Hash Join: It happens only when
sort-merge join is disabled.
3)
Shuffle Sort Merge Join:
4)
Cartesian Join
5)
Broadcasted Nested Loop Join
o
o
A view is nothing but a virtual table which takes the output of
the query and it can be used in place of tables.
A materialized view is nothing but an indirect access to the
table data by storing the results of a query in a separate schema.
Conformed fact is a table
which can be used across multiple data marts in combined with the
multiple fact tables.
What are the types of
Dimensional Modeling?
Following are the Types of Dimensions
in Data Warehouse:
- Conformed Dimension
- Outrigger Dimension
- Shrunken Dimension
- Role-playing Dimension
- Dimension to Dimension Table
- Junk Dimension
- Degenerate Dimension
- Swappable Dimension
- Step Dimension
Count all the rows in all the tables
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC
Get Comma Separated List of all columns in
table:
Select TABLE_SCHEMA, TABLE_NAME
, Stuff(
(
Select ',' + C.COLUMN_NAME
From INFORMATION_SCHEMA.COLUMNS As C
Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
And C.TABLE_NAME = T.TABLE_NAME
Order By C.ORDINAL_POSITION
For Xml Path('')
), 1, 1, '') As Columns
From INFORMATION_SCHEMA.TABLES As T
WHERE T.TABLE_NAME='TableName'
List of all Databases
Select Name from dbo.sysdatabases
select @@language AS DefaultLanguage
select @@SERVERNAME AS ServerName
List Of table without Primary Key:
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName
http://www.codingfusion.com/Post/75-Important-queries-in-SQL-Server-every-developer-should-know
Get ID of latest Record:
Select scope_Identity()
List of Keys:
SELECT DISTINCT
Constraint_Name AS [ConstraintName],
Table_Schema AS [Schema],
Table_Name AS [Table Name] FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='TableName'
Service Princiapl vs Managed Identity
Service Principal can be looked at as similar to
a service account-alike in
a more traditional on-premises application or service scenario. Managed
Identities are used for “linking” a Service Principal security object to an
Azure Resource like a Virtual Machine, Web App, Logic App or similar. For
a 1:1 relation between both, you would use a System Assigned,
whereas for a 1:multi
relation, you would use a User Assigned Managed Identity.
1.
df.show()
: It will show only the content of the dataframe.
Show()
:
df.show(n=20, truncate=True, vertical=False)
2.
df.collect()
: It will show the content and metadata of the dataframe.
3.
df.take()
: shows content and structure/metadata for a limited number of rows for a very
large dataset. It takes 1 parameter. Df.take(5)
df = sqlContext.read.format('com.databricks.spark.csv').options(header='true', inferschema='true') \
.option("delimiter", ",").option("escape", '\\').option("escape", ':').\
option("parserLib", "univocity").option("multiLine", "true").load("file.csv")
Load Parquet File in Dataframe:
To read a Parquet file into
a Pandas DataFrame, you can use the pd.read_parquet() function. The function allows you to load
data from a variety of different sources.
Import pandas as pd
url=
df=pd.read_parquet(url)
print(df.head())
df.join(ther=df2,on=commoncolumn,how=’inner’).select(df1.fieldname,df2.fieldname).show
df1.union(df2).show
sum_df1=df1.join(other=df2,on=’name’,how=’inner’).select(df1.name,df1.location,df1.quantity,df2.price)
sum_df1.groupby(‘location’).sum(sum_df1.quantity*sum_df1.price).alias(‘revenue’).show()
sum_df1,groupby(‘location’).withcolumn(“derivedcolumn”, col(sum_df1.quantity)*col(sum_df1.price))
reading json file:
dbutils.fs.put(“dbfs”,home_json,true)
spark.read.option(“multiline”,”true”).json(“filelocation”)
Create temp table to store array in table
=df.select(explode(“columnName”).alias(“columnanme))
dff = spark.read.option("header", "true").option("inferSchema", "true").option("delimiter", "|").csv("data.txt")
from pyspark.sql.functions import regexp_replace
dffs_headers = dff.dtypes
for i in dffs_headers:
columnLabel = i[0]
newColumnLabel = columnLabel.replace('^','').replace('^','') dff = dff.withColumn(newColumnLabel, regexp_replace(columnLabel, '^\\^|\\^$', ''))
if columnLabel != newColumnLabel:
dff = dff.drop(columnLabel)
display(dff)
What are the differences between Azure Synapse and Data Factory?
To begin with, Azure Synapse is an
analytics service, while Data Factory is a hybrid data integration service.
Synapse brings together data warehousing and big data analysis. Data Factory
simplifies ETL at scale. Azure Data Factory deployments allow developers to
integrate disparate data sources, but Synapse offers a unified experience to
manage, prepare, and serve data for BI and ML needs. Data Factory lacks
reporting applications like Power BI and needs improvement in terms of speed
and time. Whereas, in the case of Synapse, integration with 3rd party tools is
difficult; it lacks adequate SQL support.
Val schema=””
Val df= spark.read.format(“csv”).option(“header”,”true”).schema(schema).load(“filepath”)
Data=””
Columns=[“Name”,”Age”]
Df=spark.createdataframe(data=data,schema=Columns)
Emp_df=emp_df.join(dept_df,emp.dept_id=dept_df.dept_id,join=”inner”).show(truncate=false)
