How to create target .csv file out of a SQL query in SAP Data Services

 1. After preparing the structure of your source to query transform. Right click on the out schema in query transform and select 'Create File Format':

2. Provide the details and use it as Flag File template:

3. Make it as target in the dataflow mapping:

4. Double click the target file and set up the file location and name:

There you go!

Python to retrieve csv attachment from saved outlook msg file

1. import win32com.client
if you got this error:   ModuleNotFoundError: No module named 'win32com'
then install the module first:
    $ python3 -m pip install pywin32
  $ python3 -m pip install pypiwin32
2. Use absolute path
3. Outlook installed
4. Close(or Open and then Close) Outlook if you see error like:

for idx, file in enumerate(msg_files):
    # get file date 02-Apr-2020
    print(f'{idx+1}/{number_of_files}: {file}')
    date_str = re.split(' |\.', file)[-2]
    print(date_str)

    # Read attachments
    outlook = win32com.client.Dispatch('Outlook.Application').GetNamespace('MAPI')
    msg = outlook.OpenSharedItem(file)
    att = msg.Attachments

    for i in att:

        csv_file_name = os.path.join(csv_file_dir, f'{csv_file_prefix}-{date_str}.csv')
        i.SaveAsFile(csv_file_name)

To get error message from an error code:

import win32api
s = win32api.FormatMessage(-2147352565)
print(s)

s = win32api.FormatMessage(-2147287008)
print(s)

s = win32api.FormatMessage(-2147352567)
print(s)

Pandas dataframe to get column names as a list

Compare two SQL queries with pandas dataframe comparison

Compare the the two queries return the same data:

1. query 1 from QA
2. query 2 from PROD

Both queries use the same query statement:

SELECT *
FROM MY_HANA_VIEW
WHERE MY_CONDITION

1. Get data from DB

df_qa = pd.read_sql_query(QUERY_DIM_LEI_RATING, engine_qa)

df_prod = pd.read_sql_query(QUERY_DIM_LEI_RATING, engine_prod)

2. Sort by columns in place

columns_list = df_qa.columns.values.tolist()

df_qa.sort_values(by=columns,inplace=True)

df_prod.sort_values(by=columns, inplace = True)

3. Reset index in place

Drop the existing index and replace with the reset one

df_qa.reset_index(drop=True, inplace=True)

df_prod.reset_index(drop=True, inplace=True)

4. Assert frame equal

assert_frame_equal(df_qa, df_prod)

Note that you do not need step 2&3 if the columns have been ordered in SQL query statement, e.g.:

SELECT c1, c2, c3
FROM MY_HANA_VIEW
WHERE MY_CONDITION
order by c1, c2, c3

Load Thomson Reuters LEI to HANA with SAP DataService + Python

This blog is to consume TR REST API using SAP DS and Python to load TR LEI information to HANA database:

1. Create a DataFlow with 3 objects
    SQL: query HANA view to get the LEI identifiers which will be put into the payload of REST API
    User Defined Base Transform: this is where the Python code accessing REST API and processing coming response
    Table: the database table to save the data


2. Qquery HANA view to get the LEI identifiers


3. Set the input for Python processing


4. Bring up the "User Defined Editor", here it's Python


5. Set up the output of Python processing


We'll use 'Per Collection' mode


Save the final data to Collection(the data records collection, this is the output data)


With a solution using Python in DS, it is flexible and powerful for data loading and processing.
The only thing I dislike the is the integrated Python editor in SAP DS.

Note that SAP DS 4.2 support Python 2.7 only. Also the default library accessing REST API is urllib/urllib2. I'd like to install 'pip' and then install 'requests' for REST API consumption.

Cant' open lib libdemoabc.so

Sometimes your run into this error for Linux applications:
    Can't open lib /usr/abc/libdemoabc.so
The above error message is quite misleading. Actually the lib does exist. but some of its dependent libs are missing. Setting up the LD_LIBRARY_PATH solves the problem.

Watch files in a directory

Linux: inotifywatch in inotify-tools
Windows: FileSystemWatcher

Reference:
https://www.howtogeek.com/405468/how-to-perform-a-task-when-a-new-file-is-added-to-a-directory-in-linux/
https://gallery.technet.microsoft.com/scriptcenter/Powershell-FileSystemWatche-dfd7084b

k8s Liveness vs Readiness

Liveness: The kubelet uses liveness probes to know when to restart a Container. 
Readiness: The kubelet uses readiness probes to know when a Container is ready to start accepting traffic.

https://kubernetes.io/docs/tasks/configure-pod-container/configure-liveness-readiness-probes/

Share directory over HTTP with Python

cd the directory you want to share, and

# For Python >=2.4
python -m SimpleHTTPServer 8888
# For Python 3.x
python3 -m http.server 8888

Then you can access this http server with: http://your_host_ip:8888

PlantUML example

@startuml
hide footbox
title HANA CDN Service
database HANA
== Initialization ==
CDN -> Kafka: get Last PFCDIK of today
CDN <- Kafka: Last PFCDIK of today
note over CDN: set Last PFCDIK as 0 if no PFCDIK was found for today

|||

== Poll/Publish delta changes ==
loop every 1 minute
    CDN -> HANA: changes since Last PFCDIK of today
    CDN <- HANA: delta changes
    CDN -> Kafka: publish delta changes to topic
    CDN -> CDN: update local Last PFCDIK of today
end

Base64 encoded string to Decimal

We are using kafka-connect-jdbc to streaming data out of HANA Views, and the decimal columns are now saved as base64 encoded strings in Kafka.

To decode it with Python:

    """Convert a base64 encoded string to decimal
       b64str: the base64 encoded string
       Example: 'ATFvqA==' -> 20017064,  'JA==' -> 36
    """
    def b64_string_to_decimal(b64str):
        decoded_bytes = base64.b64decode(b64str)
        decimal_value = decimal.Decimal(int.from_bytes(decoded_bytes, byteorder='big'))
        return decimal_value

To decode it with Java:

    /*
     * Convert a base64 encoded string to decimal
     * b64str: the base64 encoded string
     * Example: 'ATFvqA==' -> 20017064,  'JA==' -> 36
     */
    public BigDecimal base64StringToDecimal(String b64String) {
        BigDecimal bigDecimal = new BigDecimal(new
BigInteger(Base64.getDecoder().decode(b64String)));
        return bigDecimal;
    }

parameter in Kotlin Primary constructor

var/val within constructor declares a property inside the class. When you do not write it, it is simply a parameter passed to the primary constructor, where you can access the parameters within the **init** block or use it initilize other properties.  Constructor parameter is never used as a property.

SAP HANA: get max record for a group

1. With Rank node

2. With Aggregation/Join node

Performance:

Rank node wins.

2d array in python3

m = 5
n = 3

a = [[0 for x in range(n)] for y in range(m)]

Or a shorter version:
a = [[0]*n for y in range(m)]

Note: shortening this to something like the following does not really work since you end up with 5 copies of the same list, so when you modify one of them, they all change.
a = [[0]*n]*m
print(a)
a[1][2] = 3
print(a)

[[0, 0, 0], [0, 0, 0], [0, 0, 0], [0, 0, 0], [0, 0, 0]]
[[0, 0, 3], [0, 0, 3], [0, 0, 3], [0, 0, 3], [0, 0, 3]]

You can use [0] * n since  Python cannot create a reference to the value 0(it's not an object) and this produces [0,0,0]. Then if you pretend you had a variable x = [0,0,0] then

c1 = x * 5
[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]


c2 = [x] * 5
[[0, 0, 0], [0, 0, 3], [0, 0, 0], [0, 0, 0], [0, 0, 0]]
[[0, 22, 0], [0, 22, 0], [0, 22, 0], [0, 22, 0], [0, 22, 0]]

download notebooks/training set/test set from Coursera

1. Go to the home of the coursera-notebook hub
2. Create a new python notebook
3. Execute !tar cvfz allfiles.tar.gz * in a cell
4. Download the archive !

Enjoy!

If the resulting archive is too big and you can't download it

Open the python notebook where you executed last command and execute the following in a cell:

!split -b 200m allfiles.tar.gz allfiles.tar.gz.part.

This will split the archive into 200Mb blocks that you can download without a problem (if there is still a problem reduce the size by changing 200m to a lower value)

Then when you have downloaded all the split files reunite them on your system using the following command line (in a linux environment, or use cmder if you are on Windows):

cat allfiles.tar.gz.part.* > allfiles.tar.gz

PS: This is in fact valid in any Jupyter-notebook hub

There is simpler way. Go to Notebook's file manager, click "New" then "Terminal", boom - you have a full terminal where you can run any commands you want (like tar).

https://github.com/coursera-dl/coursera-dl

Break training data into slices for stochastic gradient decent:

import numpy as np
n = 100
training_data = list(range(n))
mini_batch_size = 10
np.random.shuffle(training_data)
mini_batches = [training_data[k:k+mini_batch_size]
    for k in range(0, n, mini_batch_size)]
mini_batches

[[90, 5, 70, 82, 58, 2, 16, 85, 12, 35],
 [14, 54, 62, 39, 96, 73, 60, 80, 33, 89],
 [20, 38, 76, 47, 65, 42, 71, 46, 93, 34],
 [52, 64, 13, 92, 17, 49, 88, 63, 74, 23],
 [43, 25, 10, 97, 48, 68, 95, 81, 24, 31],
 [9, 32, 84, 83, 22, 87, 61, 26, 28, 99],
 [0, 67, 30, 69, 72, 45, 79, 51, 40, 55],
 [6, 15, 75, 66, 29, 3, 18, 77, 98, 21],
 [53, 44, 50, 19, 91, 8, 11, 59, 27, 56],
 [36, 94, 7, 57, 1, 37, 86, 78, 41, 4]]

Indices in Python list

You may feel uncomfortable with Python indices at the beginning. But it is really convenient if you understand it. You'd love its simplicity actually:

>>> a = list(range(10))
>>> a
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
>>> a[::2]
[0, 2, 4, 6, 8]
>>> a[1::2]
[1, 3, 5, 7, 9]
>>> a[::-2]
[9, 7, 5, 3, 1]
>>> a[1::-2]
[1]
>>> a[1:8]
[1, 2, 3, 4, 5, 6, 7]
>>> a[1:-2]
[1, 2, 3, 4, 5, 6, 7]
>>> a[::-1]
[9, 8, 7, 6, 5, 4, 3, 2, 1, 0]
>>> a[100]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
IndexError: list index out of range
>>> a[:100]
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
>>> a[7:100]
[7, 8, 9]

Reference:

https://docs.python.org/3/tutorial/introduction.html#strings

Linux library naming conventions

[root@localhost lib]# ls -lrt |grep libodbc.so
-rwxr-xr-x 1 root root 1804447 May 29  2013 libodbc.so.2.0.0
lrwxrwxrwx 1 root root      16 Nov  6  2014 libodbc.so.2 -> libodbc.so.2.0.0
lrwxrwxrwx 1 root root      16 Nov  6  2014 libodbc.so -> libodbc.so.2.0.0

Real name:  libodbc.so.2.0.0

SONAME: libodbc.so.2 

Linker name: libodbc.so

gcc "-lodbc" will seek for libodbc.so(a link or a file).

The depended library is the SONAME: libodbc.so.2

Print SONAME of a shared library:

[root@localhost lib]# objdump -p libodbc.so |grep  'SONAME' |awk -F ' '  '{print $2}'

libodbc.so.2

[root@localhost lib]# readelf -d libodbc.so |grep soname

 0x000000000000000e (SONAME)             Library soname: [libodbc.so.2]

Reference:

Every shared library has a special name called the ``soname''. The soname has the prefix ``lib'', the name of the library, the phrase ``.so'', followed by a period and a version number that is incremented whenever the interface changes (as a special exception, the lowest-level C libraries don't start with ``lib''). A fully-qualified soname includes as a prefix the directory it's in; on a working system a fully-qualified soname is simply a symbolic link to the shared library's ``real name''.

Every shared library also has a ``real name'', which is the filename containing the actual library code. The real name adds to the soname a period, a minor number, another period, and the release number. The last period and release number are optional. The minor number and release number support configuration control by letting you know exactly what version(s) of the library are installed. Note that these numbers might not be the same as the numbers used to describe the library in documentation, although that does make things easier.

In addition, there's the name that the compiler uses when requesting a library, (I'll call it the ``linker name''), which is simply the soname without any version number.

More: 

http://tldp.org/HOWTO/Program-Library-HOWTO/shared-libraries.html

makefile: execute a command and grep then awk

CP=cp
LIB_UNIXODBC=/usr/src/tpkgs/unixodbc/2.3.1/linux86w/lib/libodbc.so
RELEASE_DESTDIR=/bld/release/nsr/fb_mssql_linux/linux86w/source


$(CP) $(LIB_UNIXODBC) $(RELEASE_DESTDIR)/ddbda/odbc/$(shell objdump -p $(LIB_UNIXODBC) |grep
'SONAME' |awk -F ' ' '{print $$2}')

which equals to command line:

cp -f /usr/src/tpkgs/unixodbc/2.3.1/linux86w/lib/libodbc.so /bld/release/nsr/fb_mssql_linux/linux86w/source/ddbda/odbc/libodbc.so.2

Note:

1. shell to execute a command in a makefile

2. not like that in bash command line, the grep string is marked with single quotes.

3. there are double '$' in the awk statement.

pandas read_csv from https with Python 3.6.4

On Mac OSX, if you are using Python 3.6 and pandas to try to read a csv file via https:

california_housing_dataframe = pd.read_csv("https://storage.googleapis.com/mledu-datasets/california_housing_train.csv", sep=",")
california_housing_dataframe.describe()

 you may get an error like:
urllib.error.URLError:

To fix this issue:

Open a terminal and take a look at:

/Applications/Python 3.6/Install Certificates.command

Python 3.6 on MacOS uses an embedded version of OpenSSL, which does not use the system certificate store. More details here.

(To be explicit: MacOS users can probably resolve by opening Finder and double clicking Install Certificates.command)

Or read https csv with a workaround:

from io import StringIO

import pandas as pd
import requests


url = "https://storage.googleapis.com/mledu-datasets/california_housing_train.csv"
s = requests.get(url).text
c = pd.read_csv(StringIO(s))
print(c.head())