Tuesday, 18 April 2023

Additional Spark Interview Questions

 * How to add column to spark dataframe 

    df.withColumn("copiedfromcolumn",col(salary)-1)

    df.select(orderid,salary-1 as new_salary)

    using map to add column in spark

* Difference between map and flat map 

    - https://sparkbyexamples.com/spark/spark-map-vs-flatmap-with-examples/

    - Flat map returns more rows than the input, map returns same number of rows 

* Reduce by and Group by 

    * https://databricks.gitbooks.io/databricks-spark-knowledge-base/content/best_practices/prefer_reducebykey_over_groupbykey.html

-- Reduce by key first group data in the partition and then shuffles there by reducing amount of data which needs to be shuffled 

* How to perform basic spark operations 

* What is cost based operation in spark 

* How to optimize Athena Queries 

    - https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/

* Redshift spectrum extends support for Open source Hudi Datalakes 

    * https://aws.amazon.com/about-aws/whats-new/2020/09/amazon-redshift-spectrum-adds-support-for-querying-open-source-apache-hudi-and-delta-lake/

* Redshift spectrum and Athena

  1. https://www.upsolver.com/blog/aws-serverless-redshift-spectrum-athena
  2. Redshift spectrum need you to have a Redshift cluster , Athena does not 
  3. Redshift spectrum performance is based on your cluster size so can be faster than athena 
  4. Redshift spectrum allows you to join S3 data with your tables in cluster 

    

* How to use Spark Sql inside scala based application 

Sparkdf.CreateorReplaceTempView(viewName = "abc_table")

val agg_df = spark.sql("select order,count(*) from abc_table group by order")


Steps to work with Spark 

- create a Spark session objects 

spark = SparkSession.builder().config().getOrCreate().

df = spark.read.csv.option()

df.select.where.groupby()

df.join(df2,df(colname) === df2(colname),inner)


Spark Optimizations

* https://aws.amazon.com/blogs/big-data/best-practices-for-successfully-managing-memory-for-apache-spark-applications-on-amazon-emr/

* out of memory issue happen when Spark executur memory or parallelism is not set properly 

* R for memory intensive and C for compute intensive applications

* Understand that out of total executor memory around 90% is available for executer , Rest is system process, function memory , Reserved 

  • spark.executor.memory – Size of memory to use for each executor that runs the task.
  • spark.executor.cores – Number of virtual cores.

Take example - Suppose we want to process 200 TB of data in S3 files 

- r5.12x -- 48 Cores , 384 GB ram - 20 instances in total ( 1 for driver ) 
- Lets start with Spark executor cores - 5 cores , 
- Spark Executor  memory -  383/9 instances - 37 


spark.executor.instances - 9 * 19 - 170 

spark.default.parallelism - 170 * 5 * 2 = 1700

* Coalesce vs Repartition 
- Coalesce is used for reducing numer of partition and does not call data shuffle 

Additional Spark Optimization Techniques 
- Enabling spark dynamic query execution 
- Handles situation where Partition size is not uniform leading to work being done only by small number of executors . Example grouping by state where number of states are limited and this will leave work to small number of executors . Helps in estimating number of partitions

- Dynamically switching join stratergies - Example filtering data while joining . Join stratergy will be basd on size of dataset , but AQE will make sure plan is changed based on dymamic data size 

- Handling cases where one partition is much bigger - one task taking  much longer. General approach 







Noting down my Spark Understanding - How Plan is generated

Understanding Spark Jobs , Stages , Task. 

* Purpose here is to understand how spark execution plan is generated 

* Spark has Action and Transformation. Spark is lazy evaluation and only Action generates a Job. 

* Transformation and Action in Spark 

* Spark DataFrame is a distrubuted data structure and its immutable 

* Sql like operations are transfromation - Select , Filter, Group by , union , Intersection, distinct, repartition 



- https://spark.apache.org/docs/latest/rdd-programming-guide.html

TransformationMeaning
map(func)Return a new distributed dataset formed by passing each element of the source through a function func.
filter(func)Return a new dataset formed by selecting those elements of the source on which func returns true.
flatMap(func)Similar to map, but each input item can be mapped to 0 or more output items (so func should return a Seq rather than a single item).
mapPartitions(func)Similar to map, but runs separately on each partition (block) of the RDD, so func must be of type Iterator<T> => Iterator<U> when running on an RDD of type T.
mapPartitionsWithIndex(func)Similar to mapPartitions, but also provides func with an integer value representing the index of the partition, so func must be of type (Int, Iterator<T>) => Iterator<U> when running on an RDD of type T.
sample(withReplacementfractionseed)Sample a fraction fraction of the data, with or without replacement, using a given random number generator seed.
union(otherDataset)Return a new dataset that contains the union of the elements in the source dataset and the argument.
intersection(otherDataset)Return a new RDD that contains the intersection of elements in the source dataset and the argument.
distinct([numPartitions]))Return a new dataset that contains the distinct elements of the source dataset.
groupByKey([numPartitions])When called on a dataset of (K, V) pairs, returns a dataset of (K, Iterable<V>) pairs.
Note: If you are grouping in order to perform an aggregation (such as a sum or average) over each key, using reduceByKey or aggregateByKey will yield much better performance.
Note: By default, the level of parallelism in the output depends on the number of partitions of the parent RDD. You can pass an optional numPartitions argument to set a different number of tasks.
reduceByKey(func, [numPartitions])When called on a dataset of (K, V) pairs, returns a dataset of (K, V) pairs where the values for each key are aggregated using the given reduce function func, which must be of type (V,V) => V. Like in groupByKey, the number of reduce tasks is configurable through an optional second argument.
aggregateByKey(zeroValue)(seqOpcombOp, [numPartitions])When called on a dataset of (K, V) pairs, returns a dataset of (K, U) pairs where the values for each key are aggregated using the given combine functions and a neutral "zero" value. Allows an aggregated value type that is different than the input value type, while avoiding unnecessary allocations. Like in groupByKey, the number of reduce tasks is configurable through an optional second argument.
sortByKey([ascending], [numPartitions])When called on a dataset of (K, V) pairs where K implements Ordered, returns a dataset of (K, V) pairs sorted by keys in ascending or descending order, as specified in the boolean ascending argument.
join(otherDataset, [numPartitions])When called on datasets of type (K, V) and (K, W), returns a dataset of (K, (V, W)) pairs with all pairs of elements for each key. Outer joins are supported through leftOuterJoinrightOuterJoin, and fullOuterJoin.
cogroup(otherDataset, [numPartitions])When called on datasets of type (K, V) and (K, W), returns a dataset of (K, (Iterable<V>, Iterable<W>)) tuples. This operation is also called groupWith.
cartesian(otherDataset)When called on datasets of types T and U, returns a dataset of (T, U) pairs (all pairs of elements).
pipe(command[envVars])Pipe each partition of the RDD through a shell command, e.g. a Perl or bash script. RDD elements are written to the process's stdin and lines output to its stdout are returned as an RDD of strings.
coalesce(numPartitions)Decrease the number of partitions in the RDD to numPartitions. Useful for running operations more efficiently after filtering down a large dataset.
repartition(numPartitions)Reshuffle the data in the RDD randomly to create either more or fewer partitions and balance it across them. This always shuffles all data over the network.
repartitionAndSortWithinPartitions(partitioner)Repartition the RDD according to the given partitioner and, within each resulting partition, sort records by their keys. This is more efficient than calling repartition and then sorting within each partition because it can push the sorting down into the shuffle machinery.

Actions 

ActionMeaning
reduce(func)Aggregate the elements of the dataset using a function func (which takes two arguments and returns one). The function should be commutative and associative so that it can be computed correctly in parallel.
collect()Return all the elements of the dataset as an array at the driver program. This is usually useful after a filter or other operation that returns a sufficiently small subset of the data.
count()Return the number of elements in the dataset.
first()Return the first element of the dataset (similar to take(1)).
take(n)Return an array with the first n elements of the dataset.
takeSample(withReplacementnum, [seed])Return an array with a random sample of num elements of the dataset, with or without replacement, optionally pre-specifying a random number generator seed.
takeOrdered(n[ordering])Return the first n elements of the RDD using either their natural order or a custom comparator.
saveAsTextFile(path)Write the elements of the dataset as a text file (or set of text files) in a given directory in the local filesystem, HDFS or any other Hadoop-supported file system. Spark will call toString on each element to convert it to a line of text in the file.
saveAsSequenceFile(path)
(Java and Scala)
Write the elements of the dataset as a Hadoop SequenceFile in a given path in the local filesystem, HDFS or any other Hadoop-supported file system. This is available on RDDs of key-value pairs that implement Hadoop's Writable interface. In Scala, it is also available on types that are implicitly convertible to Writable (Spark includes conversions for basic types like Int, Double, String, etc).
saveAsObjectFile(path)
(Java and Scala)
Write the elements of the dataset in a simple format using Java serialization, which can then be loaded using SparkContext.objectFile().
countByKey()Only available on RDDs of type (K, V). Returns a hashmap of (K, Int) pairs with the count of each key.
foreach(func)Run a function func on each element of the dataset. This is usually done for side effects such as updating an Accumulator or interacting with external storage systems.
Note: modifying variables other than Accumulators outside of the foreach() may result in undefined behavior. See Understanding closures for more details.


* Each Stage will have its own DAG , DAG is spark compiler calling low level API, its difficult for us to know exact details 

* Each Actions triggers a Job in our case reading data from CSV 

* Each Job is seperated by a Shuffle operation 

* Generally wide dependency tranformation such as Group by , Repartition , will have it own stages 

* Narrow dependency tranformation in a stage will have its own tasks - example where , select , group by 

* Whenever we need to shuffle sort the results these are generally broken into stages 




dsflkadslkf

Tuesday, 17 May 2016

Microstrategy 10 - Features i found interesting


Microstrategy 10 lot of things are similar as Tableau desktop. Seems effect of Tableau popularity. However Microstrategy is great as far as enterprise security is concerned










Monday, 1 February 2016

Useful V$session sql for Sql tuning

Hi All,

Below are some v$sql which i have found useful so i have noted down here . These are compilation of sql for wide range of issues

/*How many rows the sessoin has processed */
SELECT sql_id,SQL_FULLTEXT,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,CPU_TIME/1000/1000,ELAPSED_TIME/1000/1000,PHYSICAL_READ_BYTES/1024/1024/1024 READ_BYTES_GB,executions FROM V$SQL WHERE SQL_ID IN (
'aq1d68pz6qfkq'
--select SQL_ID from v$session
--where sql_id = 'c2h4zw1a664fq'
)

/* how much temp space the session is using */
Select sample_time,event,sql_id,is_sqlid_current,temp_space_allocated/1024/1024 TEMP_MB
from v$active_session_history
where sql_id = '43rxgr4sga42b'
order by sample_time desc 

/* getting the Session id */
select sid from v$session
where sql_id = '43rxgr4sga42b'

/* On what is the session waiting */
select sid,Event,total_waits,time_waited/100,Average_wait,max_wait/100,wait_class from V$SESSION_EVENT
where sid = 584
order by total_waits desc 

/* How to find how many blocks a Table has, Crosscheck with Disk reads,Can also be used for finding size of index */ 
/* Instead of segment name but index name that should give size of index*/ 
select PARTITION_NAME,TABLESPACE_NAME,BLOCKS,BYTES/1024/1024/1024 size_of_table from dba_SEGMENTS
where SEGMENT_NAME = 'PROVIDER_DIM'
21+2+

/*Right now what it is doing */
select sample_time,sql_plan_operation,sql_plan_options,event,p1 file_no,p2 blockno,session_state,pga_allocated/1024/1024,temp_space_allocated from  v$active_session_history
where sql_id = 'gm3u8qa8kkt8v'
--and event = 'db file sequential read'
order by sample_time desc

/* what is the file and block on which its waiting as of now */ 
SELECT p1 file#, p2 block#, p3 class#,EVENT
 FROM v$session_wait where sid =87

/* to what does that block relate to */ 
SELECT relative_fno, owner, segment_name, segment_type FROM dba_extents
 WHERE file_id in  
  ( 7
 ) and 1458141 between block_id and block_id + blocks-1

/* checking index names  */ 
SELECT *
FROM all_indexes
WHERE TABLE_NAME = 'PROVIDER'

/* checking what is the depth of index */
SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS
FROM all_indexes
WHERE TABLE_NAME = 'MEMBER_DIM'
AND INDEX_NAME = 'XPKMEMBER'

/*checking how is table partitioned */

ALL_PART_TABLES

select * from all_ind_columns
WHERE index_name IN ( SELECT INDEX_NAME
FROM all_indexes
WHERE TABLE_NAME = 'PROVIDER')

/*Checking Explain plans */
select * from table (dbms_xplan.display_awr('7t1yt7gt0kmrz'))
select * from table (dbms_xplan.display_cursor('7t1yt7gt0kmrz'))
/* helps in case you cant find plan in display_cursor */
select * from v$sql_plan
where sql_id = '7t1yt7gt0kmrz'


/* Temp space your SQL is currently using*/
select 
   a.username, 
     a.sql_id,
   sum(srt.blocks * 8 / 1024) "Used Space in MB",
   sum(srt.blocks * 8 / 1024)/1024 "Used Space in GB"
from 
   v$session    a,
   v$sort_usage srt 
where 
   a.saddr = srt.session_addr 
 --  and sid in ( select  sid FROM v$SESSION WHERE OSUSER='abc')
   group by a.username,  a.sql_id

If the SQL is no longer active and you need to find what caused the issue 

select max(snap_id) from dba_hist_snapshot
11672

select * from dba_objects where object_name=upper('p_load_member_measure_detail')
182790

select distinct sql_id from dba_hist_active_sess_history where snap_id>=11672-6 and plsql_entry_object_id=182790

/* Very helpful query to check how much time a Sql took to run */
select sql_id, sum(executions_delta),
sum(elapsed_time_delta)/1000/1000, sum(cpu_time_delta)/1000/1000,sum(rows_processed_delta),
sum(buffer_gets_delta), sum(disk_reads_delta) from dba_hist_sqlstat where sql_id in (
'56xcp6m5h01b7',
'5p8c3y8mmgs94'
) and  snap_id>=11672-6
group by sql_id

select * from dba_hist_sqltext where sql_id='5p8c3y8mmgs94'

select * from table(dbms_xplan.display_awr('5p8c3y8mmgs94'))

How to Invalidate a Plan
Gather stats with No_invalidate  option to make sure new plan is taken.
execute dbms_stats.gather_table_stats('SCHEMA','PROVIDER', cascade=>true, no_invalidate=>false);