In [2]:

    

!pip install --user xlrd


    

    




Requirement already satisfied: xlrd in /gpfs/global_fs01/sym_shared/YPProdSpark/user/s1df-1767d8774d3251-73caa6cfaa60/.local/lib/python2.7/site-packages

In [16]:

    

# Setup constants if any
# FUNDS ID
FUND_ID = 'I-CJF'


    

In [4]:

    

import pandas as pd
from io import BytesIO
import requests
import json
import xlrd 

from pyspark.sql.functions import *
from pyspark.sql.types import *

from datetime import datetime
from dateutil.parser import parse

from ingest.Connectors import Connectors


    

In [5]:

    

# The code was removed by DSX for sharing.


    

In [6]:

    

# The code was removed by DSX for sharing.


    

In [46]:

    

fundNavDF = pd.read_excel(getFileFromObjectStorage('MizuhoPOC', 'I-CJF_MAI_DailyPLFiles_20170731.xlsm'), 
                              header=[0], skipinitialspace=True, 
                              skiprows=9, 
                              usecols=['Position Description', 
                                       'Bloomberg Ticker',
                                       'Stated Maturity',
                                       'Position Amount',
                                       'Base Market Value',
                                       'Local Mkt Value',
                                       'Local Notional Market Value (m)',
                                       'FX Rate',
                                       'Base Notional Market Value (m)',
                                       '% NAV NAV',
                                       'Notional % of NAV',
                                       'Notional % of Long',
                                       'Notional % of Short',
                                       'Gross Notional Value',
                                       '% Gross Notional / NAV',
                                       'Notional Long',
                                       'Notional Short',
                                       'CUSIP',
                                       'Currency',
                                       'Last Close',
                                       'Contract Size',
                                       'RIC Prefix',
                                       'Tick Value',
                                       'Price Date',                                       
                                       'Risk Date']).dropna(axis=[0,1],how='all')


fundNavRenamedDF = fundNavDF.rename(index=str, columns={"Position Description":"INVESTMENT_HLDGS",
                                                        "Bloomberg Ticker": "BB_TICK",
                                                        "Stated Maturity": "STATED_MATURITY",
                                                        "Position Amount": "POS_AMT",
                                                        "Base Market Value": "BASE_MKT_VAL",
                                                        "Local Mkt Value": "LOCAL_MKT_VAL",
                                                        "Local Notional Market Value (m)": "LOCAL_NOT_MKT_VAL",
                                                        "FX Rate": "FX_RATE",
                                                        "Base Notional Market Value (m)": "BASE_NOT_MKT_VAL",
                                                        "% NAV NAV": "PERCENT_NAV",
                                                        "Notional % of NAV": "NOT_PERCENT_OF_NAV",
                                                        "Notional % of Long": "NOT_PERCENT_OF_LONG",
                                                        "Notional % of Short": "NOT_PERCENT_OF_SHORT",
                                                        "Gross Notional Value": "GROSS_NOT_VAL",
                                                        "% Gross Notional / NAV": "PERCENT_GROSS_NOT_DIVIDE_BY_NAV",                                                        
                                                        "Notional Long": "NOT_LONG",
                                                        "Notional Short": "NOT_SHORT",
                                                        "CUSIP": "CUSIP",
                                                        "Currency": "CURRENCY",
                                                        "Last Close": "LAST_CLOSE",
                                                        "Contract Size": "CONTRACT_SIZE",
                                                        "RIC Prefix": "RIC_PREFIX",
                                                        "Tick Value": "TICK_VALUE",
                                                        "Price Date": "AS_OF_DATE",
                                                        "Risk Date": "RISK_DATE"
                                                       })

# asOfDate = pd.to_datetime('today').strftime("%Y-%m-%d")
# fundNavRenamedDF.RISK_DATE.fillna(asOfDate, inplace=True)

fundNavRenamedDF['AS_OF_DATE'] = pd.DatetimeIndex(fundNavRenamedDF['AS_OF_DATE']).strftime("%Y-%m-%d")
fundNavRenamedDF['RISK_DATE'] = pd.DatetimeIndex(fundNavRenamedDF['RISK_DATE']).strftime("%Y-%m-%d")
fundNavRenamedDF['STATED_MATURITY'] = pd.DatetimeIndex(fundNavRenamedDF['STATED_MATURITY']).strftime("%Y-%m-%d")

# Drop the last 2 rows , fotter rows
fundNavRenamedDF = fundNavRenamedDF[:-2]

fundNavRenamedDF = fundNavRenamedDF.fillna('')

fundNavRenamedDF.head(12)
# fundNavRenamedDF.dtypes


    

    
Out[46]:






  

    

      

      
INVESTMENT_HLDGS
      
BB_TICK
      
STATED_MATURITY
      
POS_AMT
      
BASE_MKT_VAL
      
LOCAL_MKT_VAL
      
LOCAL_NOT_MKT_VAL
      
FX_RATE
      
BASE_NOT_MKT_VAL
      
PERCENT_NAV
      
...
      
NOT_LONG
      
NOT_SHORT
      
CUSIP
      
CURRENCY
      
LAST_CLOSE
      
CONTRACT_SIZE
      
RIC_PREFIX
      
TICK_VALUE
      
AS_OF_DATE
      
RISK_DATE
    
  
  

    

      
0
      
Futures
      

      
NaT
      
1193
      

      

      

      

      
132188
      
0
      
...
      
465%/263MM
      
232%/131MM
      

      

      

      

      

      

      
NaT
      
NaT
    
    

      
1
      
Interest Rate Futures
      

      
NaT
      
54
      

      

      

      

      
57078
      
0
      
...
      
195%/111MM
      
94%/53MM
      

      

      

      

      

      

      
NaT
      
NaT
    
    

      
2
      
BAX
      

      
NaT
      
-58
      

      

      

      

      
-46182
      
0
      
...
      
0%/0MM
      
81%/46MM
      

      

      

      

      

      

      
NaT
      
NaT
    
    

      
3
      
BANK ACCEPT MAR 18 03/19/2018
      
BAH8
      
2018-03-19
      
-15
      

      

      
-14940
      
1.25055
      
-11946
      
0
      
...
      

      
-11946444
      
BAH820181
      
CAD
      
98.39
      
1e+06
      
BAX
      
12.5
      
2017-07-31
      
2017-07-31
    
    

      
4
      
BANK ACCEPT JUN 18 06/18/2018
      
BAM8
      
2018-06-18
      
-13
      

      

      
-12946
      
1.25055
      
-10352
      
0
      
...
      

      
-10351895
      
BAM820185
      
CAD
      
98.325
      
1e+06
      
BAX
      
12.5
      
2017-07-31
      
2017-07-31
    
    

      
5
      
BANK ACCEPT SEP 18 09/17/2018
      
BAU8
      
2018-09-17
      
-13
      

      

      
-12944
      
1.25055
      
-10350
      
0
      
...
      

      
-10350466
      
BAU820186
      
CAD
      
98.27
      
1e+06
      
BAX
      
12.5
      
2017-07-31
      
2017-07-31
    
    

      
6
      
BANK ACCEPT DEC 18 12/17/2018
      
BAZ8
      
2018-12-17
      
-17
      

      

      
-16924
      
1.25055
      
-13534
      
0
      
...
      

      
-13533525
      
BAZ820181
      
CAD
      
98.22
      
1e+06
      
BAX
      
12.5
      
2017-07-31
      
2017-07-31
    
    

      
8
      
ED
      

      
NaT
      
26
      

      

      

      

      
25897
      
0
      
...
      
49%/28MM
      
4%/2MM
      

      

      

      

      

      

      
NaT
      
NaT
    
    

      
9
      
EURO$ 90 DAY MAR 18 03/19/2018
      
EDH8
      
2018-03-19
      
6
      

      

      
5977
      
1
      
5977
      
0
      
...
      
5977050
      

      
EDH820182
      
USD
      
98.47
      
1e+06
      
ED
      
12.5
      
2017-07-31
      
2017-07-31
    
    

      
10
      
EURO$ 90 DAY JUN 18 06/18/2018
      
EDM8
      
2018-06-18
      
15
      

      

      
14940
      
1
      
14940
      
0
      
...
      
14940375
      

      
EDM820186
      
USD
      
98.41
      
1e+06
      
ED
      
12.5
      
2017-07-31
      
2017-07-31
    
    

      
11
      
EURO$ 90 DAY SEP 18 09/17/2018
      
EDU8
      
2018-09-17
      
7
      

      

      
6971
      
1
      
6971
      
0
      
...
      
6971038
      

      
EDU820187
      
USD
      
98.345
      
1e+06
      
ED
      
12.5
      
2017-07-31
      
2017-07-31
    
    

      
12
      
EURO$ 90 DAY DEC 18 12/17/2018
      
EDZ8
      
2018-12-17
      
-2
      

      

      
-1991
      
1
      
-1991
      
0
      
...
      

      
-1991325
      
EDZ820182
      
USD
      
98.265
      
1e+06
      
ED
      
12.5
      
2017-07-31
      
2017-07-31
    
  

12 rows × 25 columns

In [47]:

    

spark = SparkSession.builder.getOrCreate() 

def build_schema():
    """Build and return a schema to use for the sample data."""
    schema = StructType(
        [               
            StructField("INVESTMENT_HLDGS",  StringType(), True),
            StructField("BB_TICK", StringType(), True),
            StructField("STATED_MATURITY", StringType(), True),
            StructField("POS_AMT",  StringType(), True),
            StructField("BASE_MKT_VAL",  StringType(), True),
            StructField("LOCAL_MKT_VAL",  StringType(), True),
            StructField("LOCAL_NOT_MKT_VAL",  StringType(), True),
            StructField("FX_RATE",  StringType(), True),
            StructField("BASE_NOT_MKT_VAL",  StringType(), True),
            StructField("PERCENT_NAV",  StringType(), True),
            StructField("NOT_PERCENT_OF_NAV",  StringType(), True),
            StructField("NOT_PERCENT_OF_LONG",  StringType(), True),
            StructField("NOT_PERCENT_OF_SHORT",  StringType(), True),
            StructField("GROSS_NOT_VAL",  StringType(), True),
            StructField("PERCENT_GROSS_NOT_DIVIDE_BY_NAV",  StringType(), True),
            StructField("NOT_LONG",  StringType(), True),
            StructField("NOT_SHORT",  StringType(), True),
            StructField("CUSIP",  StringType(), True),
            StructField("CURRENCY",  StringType(), True),
            StructField("LAST_CLOSE",  StringType(), True),
            StructField("CONTRACT_SIZE",  StringType(), True),
            StructField("RIC_PREFIX",  StringType(), True),
            StructField("TICK_VALUE",  StringType(), True),
            StructField("AS_OF_DATE",  StringType(), True),
            StructField("RISK_DATE",  StringType(), True)
        ]
    )
    return schema


fundNavDFSparkDF = spark.createDataFrame(fundNavRenamedDF, schema=build_schema()) \
    .withColumn("FUND_ID", lit(FUND_ID))


fundNavDFSparkDF.printSchema()
fundNavDFSparkDF.head(10)


    

    




root
 |-- INVESTMENT_HLDGS: string (nullable = true)
 |-- BB_TICK: string (nullable = true)
 |-- STATED_MATURITY: string (nullable = true)
 |-- POS_AMT: string (nullable = true)
 |-- BASE_MKT_VAL: string (nullable = true)
 |-- LOCAL_MKT_VAL: string (nullable = true)
 |-- LOCAL_NOT_MKT_VAL: string (nullable = true)
 |-- FX_RATE: string (nullable = true)
 |-- BASE_NOT_MKT_VAL: string (nullable = true)
 |-- PERCENT_NAV: string (nullable = true)
 |-- NOT_PERCENT_OF_NAV: string (nullable = true)
 |-- NOT_PERCENT_OF_LONG: string (nullable = true)
 |-- NOT_PERCENT_OF_SHORT: string (nullable = true)
 |-- GROSS_NOT_VAL: string (nullable = true)
 |-- PERCENT_GROSS_NOT_DIVIDE_BY_NAV: string (nullable = true)
 |-- NOT_LONG: string (nullable = true)
 |-- NOT_SHORT: string (nullable = true)
 |-- CUSIP: string (nullable = true)
 |-- CURRENCY: string (nullable = true)
 |-- LAST_CLOSE: string (nullable = true)
 |-- CONTRACT_SIZE: string (nullable = true)
 |-- RIC_PREFIX: string (nullable = true)
 |-- TICK_VALUE: string (nullable = true)
 |-- AS_OF_DATE: string (nullable = true)
 |-- RISK_DATE: string (nullable = true)
 |-- FUND_ID: string (nullable = false)







    
Out[47]:





[Row(INVESTMENT_HLDGS=u'Futures', BB_TICK=u'', STATED_MATURITY=u'NaT', POS_AMT=u'1193.0', BASE_MKT_VAL=u'', LOCAL_MKT_VAL=u'', LOCAL_NOT_MKT_VAL=u'', FX_RATE=u'', BASE_NOT_MKT_VAL=u'132188.0', PERCENT_NAV=u'0.0', NOT_PERCENT_OF_NAV=u'233.2', NOT_PERCENT_OF_LONG=u'82.26', NOT_PERCENT_OF_SHORT=u'99.91', GROSS_NOT_VAL=u'3.94728928E8', PERCENT_GROSS_NOT_DIVIDE_BY_NAV=u'696.37', NOT_LONG=u'465%/263MM', NOT_SHORT=u'232%/131MM', CUSIP=u'', CURRENCY=u'', LAST_CLOSE=u'', CONTRACT_SIZE=u'', RIC_PREFIX=u'', TICK_VALUE=u'', AS_OF_DATE=u'NaT', RISK_DATE=u'NaT', FUND_ID=u'I-CJF'),
 Row(INVESTMENT_HLDGS=u'Interest Rate Futures', BB_TICK=u'', STATED_MATURITY=u'NaT', POS_AMT=u'54.0', BASE_MKT_VAL=u'', LOCAL_MKT_VAL=u'', LOCAL_NOT_MKT_VAL=u'', FX_RATE=u'', BASE_NOT_MKT_VAL=u'57078.0', PERCENT_NAV=u'0.0', NOT_PERCENT_OF_NAV=u'100.69', NOT_PERCENT_OF_LONG=u'34.51', NOT_PERCENT_OF_SHORT=u'40.67', GROSS_NOT_VAL=u'1.63956421E8', PERCENT_GROSS_NOT_DIVIDE_BY_NAV=u'289.25', NOT_LONG=u'195%/111MM', NOT_SHORT=u'94%/53MM', CUSIP=u'', CURRENCY=u'', LAST_CLOSE=u'', CONTRACT_SIZE=u'', RIC_PREFIX=u'', TICK_VALUE=u'', AS_OF_DATE=u'NaT', RISK_DATE=u'NaT', FUND_ID=u'I-CJF'),
 Row(INVESTMENT_HLDGS=u'BAX', BB_TICK=u'', STATED_MATURITY=u'NaT', POS_AMT=u'-58.0', BASE_MKT_VAL=u'', LOCAL_MKT_VAL=u'', LOCAL_NOT_MKT_VAL=u'', FX_RATE=u'', BASE_NOT_MKT_VAL=u'-46182.0', PERCENT_NAV=u'0.0', NOT_PERCENT_OF_NAV=u'-81.47', NOT_PERCENT_OF_LONG=u'', NOT_PERCENT_OF_SHORT=u'35.15', GROSS_NOT_VAL=u'4.618233E7', PERCENT_GROSS_NOT_DIVIDE_BY_NAV=u'81.47', NOT_LONG=u'0%/0MM', NOT_SHORT=u'81%/46MM', CUSIP=u'', CURRENCY=u'', LAST_CLOSE=u'', CONTRACT_SIZE=u'', RIC_PREFIX=u'', TICK_VALUE=u'', AS_OF_DATE=u'NaT', RISK_DATE=u'NaT', FUND_ID=u'I-CJF'),
 Row(INVESTMENT_HLDGS=u'BANK ACCEPT MAR 18 03/19/2018', BB_TICK=u'BAH8', STATED_MATURITY=u'2018-03-19', POS_AMT=u'-15.0', BASE_MKT_VAL=u'', LOCAL_MKT_VAL=u'', LOCAL_NOT_MKT_VAL=u'-14940.0', FX_RATE=u'1.25055', BASE_NOT_MKT_VAL=u'-11946.0', PERCENT_NAV=u'0.0', NOT_PERCENT_OF_NAV=u'-21.08', NOT_PERCENT_OF_LONG=u'', NOT_PERCENT_OF_SHORT=u'9.09', GROSS_NOT_VAL=u'-1.1946444E7', PERCENT_GROSS_NOT_DIVIDE_BY_NAV=u'-21.08', NOT_LONG=u'', NOT_SHORT=u'-11946444', CUSIP=u'BAH820181', CURRENCY=u'CAD', LAST_CLOSE=u'98.39', CONTRACT_SIZE=u'1000000.0', RIC_PREFIX=u'BAX', TICK_VALUE=u'12.5', AS_OF_DATE=u'2017-07-31', RISK_DATE=u'2017-07-31', FUND_ID=u'I-CJF'),
 Row(INVESTMENT_HLDGS=u'BANK ACCEPT JUN 18 06/18/2018', BB_TICK=u'BAM8', STATED_MATURITY=u'2018-06-18', POS_AMT=u'-13.0', BASE_MKT_VAL=u'', LOCAL_MKT_VAL=u'', LOCAL_NOT_MKT_VAL=u'-12946.0', FX_RATE=u'1.25055', BASE_NOT_MKT_VAL=u'-10352.0', PERCENT_NAV=u'0.0', NOT_PERCENT_OF_NAV=u'-18.26', NOT_PERCENT_OF_LONG=u'', NOT_PERCENT_OF_SHORT=u'7.88', GROSS_NOT_VAL=u'-1.0351895E7', PERCENT_GROSS_NOT_DIVIDE_BY_NAV=u'-18.26', NOT_LONG=u'', NOT_SHORT=u'-10351895', CUSIP=u'BAM820185', CURRENCY=u'CAD', LAST_CLOSE=u'98.325', CONTRACT_SIZE=u'1000000.0', RIC_PREFIX=u'BAX', TICK_VALUE=u'12.5', AS_OF_DATE=u'2017-07-31', RISK_DATE=u'2017-07-31', FUND_ID=u'I-CJF'),
 Row(INVESTMENT_HLDGS=u'BANK ACCEPT SEP 18 09/17/2018', BB_TICK=u'BAU8', STATED_MATURITY=u'2018-09-17', POS_AMT=u'-13.0', BASE_MKT_VAL=u'', LOCAL_MKT_VAL=u'', LOCAL_NOT_MKT_VAL=u'-12944.0', FX_RATE=u'1.25055', BASE_NOT_MKT_VAL=u'-10350.0', PERCENT_NAV=u'0.0', NOT_PERCENT_OF_NAV=u'-18.26', NOT_PERCENT_OF_LONG=u'', NOT_PERCENT_OF_SHORT=u'7.88', GROSS_NOT_VAL=u'-1.0350466E7', PERCENT_GROSS_NOT_DIVIDE_BY_NAV=u'-18.26', NOT_LONG=u'', NOT_SHORT=u'-10350466', CUSIP=u'BAU820186', CURRENCY=u'CAD', LAST_CLOSE=u'98.27', CONTRACT_SIZE=u'1000000.0', RIC_PREFIX=u'BAX', TICK_VALUE=u'12.5', AS_OF_DATE=u'2017-07-31', RISK_DATE=u'2017-07-31', FUND_ID=u'I-CJF'),
 Row(INVESTMENT_HLDGS=u'BANK ACCEPT DEC 18 12/17/2018', BB_TICK=u'BAZ8', STATED_MATURITY=u'2018-12-17', POS_AMT=u'-17.0', BASE_MKT_VAL=u'', LOCAL_MKT_VAL=u'', LOCAL_NOT_MKT_VAL=u'-16924.0', FX_RATE=u'1.25055', BASE_NOT_MKT_VAL=u'-13534.0', PERCENT_NAV=u'0.0', NOT_PERCENT_OF_NAV=u'-23.88', NOT_PERCENT_OF_LONG=u'', NOT_PERCENT_OF_SHORT=u'10.3', GROSS_NOT_VAL=u'-1.3533525E7', PERCENT_GROSS_NOT_DIVIDE_BY_NAV=u'-23.88', NOT_LONG=u'', NOT_SHORT=u'-13533525', CUSIP=u'BAZ820181', CURRENCY=u'CAD', LAST_CLOSE=u'98.22', CONTRACT_SIZE=u'1000000.0', RIC_PREFIX=u'BAX', TICK_VALUE=u'12.5', AS_OF_DATE=u'2017-07-31', RISK_DATE=u'2017-07-31', FUND_ID=u'I-CJF'),
 Row(INVESTMENT_HLDGS=u'ED', BB_TICK=u'', STATED_MATURITY=u'NaT', POS_AMT=u'26.0', BASE_MKT_VAL=u'', LOCAL_MKT_VAL=u'', LOCAL_NOT_MKT_VAL=u'', FX_RATE=u'', BASE_NOT_MKT_VAL=u'25897.0', PERCENT_NAV=u'0.0', NOT_PERCENT_OF_NAV=u'45.69', NOT_PERCENT_OF_LONG=u'8.71', NOT_PERCENT_OF_SHORT=u'1.52', GROSS_NOT_VAL=u'2.9879788E7', PERCENT_GROSS_NOT_DIVIDE_BY_NAV=u'52.71', NOT_LONG=u'49%/28MM', NOT_SHORT=u'4%/2MM', CUSIP=u'', CURRENCY=u'', LAST_CLOSE=u'', CONTRACT_SIZE=u'', RIC_PREFIX=u'', TICK_VALUE=u'', AS_OF_DATE=u'NaT', RISK_DATE=u'NaT', FUND_ID=u'I-CJF'),
 Row(INVESTMENT_HLDGS=u'EURO$ 90 DAY MAR 18 03/19/2018', BB_TICK=u'EDH8', STATED_MATURITY=u'2018-03-19', POS_AMT=u'6.0', BASE_MKT_VAL=u'', LOCAL_MKT_VAL=u'', LOCAL_NOT_MKT_VAL=u'5977.0', FX_RATE=u'1.0', BASE_NOT_MKT_VAL=u'5977.0', PERCENT_NAV=u'0.0', NOT_PERCENT_OF_NAV=u'10.54', NOT_PERCENT_OF_LONG=u'1.87', NOT_PERCENT_OF_SHORT=u'', GROSS_NOT_VAL=u'5977050.0', PERCENT_GROSS_NOT_DIVIDE_BY_NAV=u'10.54', NOT_LONG=u'5977050', NOT_SHORT=u'', CUSIP=u'EDH820182', CURRENCY=u'USD', LAST_CLOSE=u'98.47', CONTRACT_SIZE=u'1000000.0', RIC_PREFIX=u'ED', TICK_VALUE=u'12.5', AS_OF_DATE=u'2017-07-31', RISK_DATE=u'2017-07-31', FUND_ID=u'I-CJF'),
 Row(INVESTMENT_HLDGS=u'EURO$ 90 DAY JUN 18 06/18/2018', BB_TICK=u'EDM8', STATED_MATURITY=u'2018-06-18', POS_AMT=u'15.0', BASE_MKT_VAL=u'', LOCAL_MKT_VAL=u'', LOCAL_NOT_MKT_VAL=u'14940.0', FX_RATE=u'1.0', BASE_NOT_MKT_VAL=u'14940.0', PERCENT_NAV=u'0.0', NOT_PERCENT_OF_NAV=u'26.36', NOT_PERCENT_OF_LONG=u'4.67', NOT_PERCENT_OF_SHORT=u'', GROSS_NOT_VAL=u'1.4940375E7', PERCENT_GROSS_NOT_DIVIDE_BY_NAV=u'26.36', NOT_LONG=u'14940375', NOT_SHORT=u'', CUSIP=u'EDM820186', CURRENCY=u'USD', LAST_CLOSE=u'98.41', CONTRACT_SIZE=u'1000000.0', RIC_PREFIX=u'ED', TICK_VALUE=u'12.5', AS_OF_DATE=u'2017-07-31', RISK_DATE=u'2017-07-31', FUND_ID=u'I-CJF')]

In [42]:

    

dashDBloadOptions = { 
                    Connectors.DASHDB.HOST              : dashCredentials["host"],
                    Connectors.DASHDB.DATABASE          : dashCredentials["db"],
                    Connectors.DASHDB.USERNAME          : dashCredentials["username"],
                    Connectors.DASHDB.PASSWORD          : dashCredentials["password"],
                    Connectors.DASHDB.SOURCE_TABLE_NAME : dashCredentials["REF_FUND_TABLE"],
}

refFundDF = spark.read.format("com.ibm.spark.discover").options(**dashDBloadOptions).load()
refFundDF.printSchema()
refFundDF.show(1)


    

    




root
 |-- ID: string (nullable = false)

+-----+
|   ID|
+-----+
|I-CJF|
+-----+
only showing top 1 row

In [50]:

    

spark.conf.set("spark.sql.crossJoin.enabled", True)
fundNavDFJoinSparkDF = fundNavDFSparkDF.join(refFundDF, 
                                               fundNavDFSparkDF.FUND_ID == refFundDF.ID, "inner")\
                                        .select(
                                            refFundDF.ID.alias("FUND_ID"),
                                            fundNavDFSparkDF.INVESTMENT_HLDGS,
                                            fundNavDFSparkDF.BB_TICK,
                                            fundNavDFSparkDF.STATED_MATURITY,
                                            fundNavDFSparkDF.POS_AMT,
                                            fundNavDFSparkDF.BASE_MKT_VAL,
                                            fundNavDFSparkDF.LOCAL_MKT_VAL,
                                            fundNavDFSparkDF.LOCAL_NOT_MKT_VAL,
                                            fundNavDFSparkDF.FX_RATE,
                                            fundNavDFSparkDF.BASE_NOT_MKT_VAL,
                                            fundNavDFSparkDF.PERCENT_NAV,
                                            fundNavDFSparkDF.NOT_PERCENT_OF_NAV,
                                            fundNavDFSparkDF.NOT_PERCENT_OF_LONG,
                                            fundNavDFSparkDF.NOT_PERCENT_OF_SHORT,
                                            fundNavDFSparkDF.GROSS_NOT_VAL,
                                            fundNavDFSparkDF.PERCENT_GROSS_NOT_DIVIDE_BY_NAV,
                                            fundNavDFSparkDF.NOT_LONG,
                                            fundNavDFSparkDF.NOT_SHORT,
                                            fundNavDFSparkDF.CUSIP,
                                            fundNavDFSparkDF.CURRENCY,
                                            fundNavDFSparkDF.LAST_CLOSE,
                                            fundNavDFSparkDF.CONTRACT_SIZE,
                                            fundNavDFSparkDF.RIC_PREFIX,
                                            fundNavDFSparkDF.TICK_VALUE,
                                            fundNavDFSparkDF.AS_OF_DATE,
                                            fundNavDFSparkDF.RISK_DATE
                                            )

fundNavDFJoinSparkDF.count()


    

    
Out[50]:





249

In [49]:

    

# Connection to Dash DB for writing the data
dashdbsaveoption = {
                     Connectors.DASHDB.HOST              : dashCredentials["host"],
                     Connectors.DASHDB.DATABASE          : dashCredentials["db"],
                     Connectors.DASHDB.USERNAME          : dashCredentials["username"],
                     Connectors.DASHDB.PASSWORD          : dashCredentials["password"],
                     Connectors.DASHDB.TARGET_TABLE_NAME : dashCredentials["tableName"],
                     Connectors.DASHDB.TARGET_WRITE_MODE : 'merge' 
}

fundNavDashDBDF = fundNavDFJoinSparkDF.write.format("com.ibm.spark.discover").options(**dashdbsaveoption).save()


    

In [ ]: