How to upload custom visualization to Oracle DV server version

http://HOSTNAME:7780/dv/ui/home.jsp?consoletab=customPluginsTab&pageid=console

Parent-Child table for OBIEE 11-12 on MS SQL server

Script for generation data of parent-child relationship table


CREATE TABLE [dbo].[PC_FIN_CFO](
[MEMBER_KEY] varchar(36) NOT NULL,
[ANCESTOR_KEY] varchar(36) NULL,
[DISTANCE] [int] NULL,
[IS_LEAF] [int] NULL
) ON [PRIMARY]

/**********************************************************/

delete from ALT_PC_FIN_CFO;

DECLARE @id varchar(36);

DECLARE db_cursor CURSOR FOR
select row_wid from DC_FIN_CFO

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id

WHILE @@FETCH_STATUS = 0
BEGIN
--------------

WITH tblParent AS
(
    SELECT DC_FIN_CFO.*, 0 as generation,/*
ISNULL((select 1
where not exists (
select 'x' from DC_FIN_CFO where
parent_row_wid=@id)),0) */ 0 isleaf
,@id START_ROW_WID
        FROM DC_FIN_CFO WHERE ROW_WID = @id
    UNION ALL
    SELECT DC_FIN_CFO.*, generation+1,
ISNULL((select 1
where not exists (
select 'x' from DC_FIN_CFO where
parent_row_wid=@id)),0) isleaf
,@id START_ROW_WID
        FROM DC_FIN_CFO  JOIN tblParent  ON DC_FIN_CFO.ROW_WID = tblParent.PARENT_ROW_WID
)
insert into ALT_PC_FIN_CFO(MEMBER_KEY,ANCESTOR_KEY,DISTANCE,IS_LEAF)
SELECT START_ROW_WID, ROW_WID, generation, isleaf  FROM  tblParent
OPTION(MAXRECURSION 32767)

--------------
FETCH NEXT FROM db_cursor INTO @id
--------------
END

CLOSE db_cursor
DEALLOCATE db_cursor


insert into ALT_PC_FIN_CFO(MEMBER_KEY,ANCESTOR_KEY,DISTANCE,IS_LEAF)
select ROW_WID, PARENT_ROW_WID, null, 0 from DC_FIN_CFO where PARENT_ROW_WID is null;

Spark 2 - select in dbtable

Dataset tabs = ss.read()
 .format("jdbc")
 .option("url", "jdbc:oracle:thin:@localhost:1521:orcl")
 .option("dbtable", "(select s.owner,t.table_name, s.bytes, s.blocks from sparkdt.segments s, sparkdt.tables t where s.owner=t.owner and s.segment_name=t.table_name) tabisegs")
 .option("user", "sparkdt")
 .option("password", "sparkdt")
 .load();

Get JavaSparkContext in Spark2

 SparkSession ss = SparkSession.builder().appName("JavaExample").master("local[*]").getOrCreate();

  JavaSparkContext jsc = new JavaSparkContext(ss.sparkContext());

Convert dataframe/dataset to libsvm format row in Spark 2.0 in Java

Creating typed dataset. No difference, it's just a type that you pass into map function later

            SparkSession ss = SparkSession.builder().appName("JavaALSExample").master("local[*]").getOrCreate();  
            Dataset dsout = ss.read().format("parquet").load("hdfs://spark16/user/gollum/ads_par_clear").as(Encoders.bean(AdsFrame.class));  
            dsout.printSchema();  
            dsout.show();  

Create struct of required format

 StructType libsvmSchema = new StructType().add("label", "double").add("features", new VectorUDT());  

 Convert

            Dataset dsLibsvm = ss.createDataFrame(  
            dsout.javaRDD().map(new Function() {  
                 public Row call(AdsFrame a) {  
                      Double label = (double) 0;  
                      Vector currentRow = Vectors.dense(a.getAlti(), a.getLati(), a.getLongi(), a.getSpeed(), a.getTrck() );  
                      return RowFactory.create(label, currentRow);  
                 }  
                 }),  
            libsvmSchema);  

I have not tried it in [Structured] streaming, but can works too

Hive metadata repository for Spark usage

Don't forget to add path to the Hive configuration folder in spark-defaults.conf:

spark.driver.extraClassPath /opt/hive/conf/

It must appears in the Spark application UI on Environment tab.

Without it Spark can not found you config and will be use defaults and create derby.log and metastore_db in current folder every time then you use hive context.

Simple way to update rows in DataFrame

Lifehack for oldschool peoples, who prefer RDBMS-like approach.

  DataFrame reslt = sqlContext.createDataFrame(  
   srcDatafr.javaRDD().map(row -> {  
   return RowFactory.create(row.get(0), row.get(1), row.get(2), "You value", row.get(4) );  
   }), srcDatafr.schema());  
   reslt.printSchema();  
   reslt.show();   
   }     

Very beautiful solution, get it here.