DEV Community: Pavlo Paska

How To Make Parallel API calls in JSPython

Pavlo Paska — Thu, 13 Oct 2022 07:00:21 +0000

It is quite a common scenario when you have to make multiple an API calls in parallel. Making parallel calls will reduce the latency and improve the performance of your solution. Either you are looking to speed up a data loading process or perform some sort of load/stress testing.
In Javascript, you can spin up multiple Promises and then use the function Promise.all() to wait for all of them to succeed. Or you can use RxJS, streams or controlled callbacks

JSPython was designed to evaluate all instructions synchronously. Mainly because of simplicity, it looks like Python users - data engineers and analysts don't like the complexity that comes with Promises, Streams or callbacks. JSPython awaits/resolves all promises at its core. So, you won't have any Promise anywhere in the JSPython runtime (apart from some edge cases).

So, how to run code parallel, then?
The answer is RxJs. You can use RxJs functions to create observable sequences, then combine response values they emitted (with forkJoin) and convert them back to Promise (which will be automatically evaluated in JSPython)

The following JSPython code shows this approach in action. This example makes five parallel requests and returns an array of responses along with the corresponding requestId

from 'rxjs' import forkJoin, lastValueFrom, map

ids = [2, 7, 4, 9, 5]

httpRequests$ = ids
    .map(
        requestId => httpRequest$("GET", "https://jsonplaceholder.typicode.com/posts/" + requestId)
                                            .pipe(
                                                map(r => {requestId, response: r.data})
                                            )
    )

return lastValueFrom(forkJoin(httpRequests$))

Let's go through the code.

Import RxJs functions
Create an array of ids.
Use map function to transform an array of ids into an array of Observables.
Use the httpRequest$ function to fetch data from a server. The asynchronous built-in function sends an HTTP request, and returns an Observable that emits the requested data when the response is received.
Then we use observable's pipe(map(...)) and transform result, by adding a requestId and response
Then we wait for all observables to complete and combine responses in the array. We use forkJoin for this
Then convert observable into Promise with function lastValueFrom. Then, JSPython natively resolves Promise into a synchronous result

Run

Press Run and you will see the result

Working example

A working example can be found here
https://run.worksheet.systems/data-studio/app/guest/jspy-playground?file=http-calls%2Fparallel-http-requests.jspy
or if you are using chrome extention
chrome-extension://dkhnlgcpdiifkfjdjceogenclkdfbonh/index.html#/data-studio/app/guest/jspy-playground?file=http-calls%2Fparallel-http-requests.jspy

Sequential vs. Parallel API Calls

In another example, I want to show two different functions where one of them run_Sequential is making https requests sequential and function run_Parallel is doing same things but in parallel

from 'rxjs' import forkJoin, lastValueFrom, map

ids = [2, 7, 4, 9, 5]

async def run_Sequential():
    data = []

    for requestId in ids:
        response = httpGet("https://jsonplaceholder.typicode.com/posts/" + requestId)
        data.push({requestId, response})

    return data

async def run_Parallel():
    httpRequests$ = ids
        .map(
            requestId => httpRequest$("GET", "https://jsonplaceholder.typicode.com/posts/" + requestId)
                                                .pipe(
                                                    map(r => {requestId, response: r.data})
                                                )
        )

    return lastValueFrom(forkJoin(httpRequests$))

if __env.entryFunction == '':
    return {
            sequential: run_Sequential(),
            parallel: run_Parallel()
        }

Working example is here
https://run.worksheet.systems/data-studio/app/guest/jspy-playground?file=http-calls%2Fsequential-vs-parallel-http-calls.jspy.
Just Run this code and open chrome dev-tools - network tab. Where you can clearly see the difference

WORKSHEETS Data Studio

WORKSHEETS Data Studio is a Low Code Data Management Platform for Data Analysis, Data Processing, SQL Database Management and RAPID App Development.

Online SQL Client for low code data management

Pavlo Paska — Sun, 21 Aug 2022 11:37:00 +0000

There are many good tools to access and work with relational databases. My favourite ones were MS SQL Management Studio and Azure Data Studio. However, several features can make exploring, analysing and working with SQL databases much better and easier.

We wanted to go a bit further, beyond a SQL query, beyond simple views or CRUD operations. We wanted to give people tools to leverage SQL robustness and power to allow anybody with fundamental data engineering skills (like citizen developers, business analysts, data analysts and even business people) a low code platform to make robust data management tasks much more straightforward.

We’ve introduced an online SQL client as a core part of WORKSHEETS Data Studio — our low code data management platform. Keeping your data in the database is fundamental to any data-centric IT solution.

WORKSHEETS Data Studio can be accessed in your browser straight away. So, to get started, you don’t need anything to install.

Short video intro

We have apparent features, like a tree with data objects, SQL editor with auto-complete, a result panel and everything else you can find in other conventional RMDB tools. However, I would like to highlight the following features:

Share and Collaborate

Since WORKSHEETS Data Studio is a web (browser-based) tool, sharing and collaboration are native features. You can create appealing user stories and share them with the world with a single URL. A URL contains all your filters, sorting and columns and makes it very useful when you want to share a link. This will allow other people to see the same information as you see it. In addition, you can permit access to your database for individual users and groups or even grant public access.

Easily navigate and search.

VSCode-inspired WORKSHEET Data Studio layout, and you’ll find a lot of similarities, e.g. Tabs, Sidebar etc. However, the most incredible feature is a search box. A single textbox where you can find and navigate to any database object (table/view/stored procedure) within all databases you are connected to. So you don’t have to click and scroll anymore. Just type in whatever you are looking for

Data Visualization and Data Analysis

Once you have found your table/view, you can see data in the excel-like data grids and even plot charts for quick exploratory data analysis and modifications.

Excel-like filter for each column.

By default, we are showing only the first 1000 rows (you can remove this restriction). So, you can use your filters for each column. Here are two useful features:

A unique list of values
You can workout either this column is unique or not

All filters added up, and a summary is shown in the bottom left corner

From the picture above, you can tell that the table SampleSupersureSales has 8,399 rows. And you can see 821 rows WHERE Customer Segment is ‘Small Business’ or ‘Consumer’ and Product Category equal ‘Technology’. Also, from the header filter (like in excel), you can find a breakdown of 21 countries.

Join tables together

We know how important it is to combine pieces of information and get a holistic data view. You can effortlessly join or extend tables even if you don’t have DDL access. Just use a visual editor. Then effortlessly share with your colleagues or customers with a simple URL

You can use a visual Query Manager to join tables/views, select columns from a joined table and specify field expressions if needed.

Then, from the visual query designer, you can generate SQL Query or JSPython code with SQL-Data-API.

Generated SQL Code will look like

Or you can easily plot any table data into a graph for better visibility and storytelling.

Linked Views

Joining, filtering and customising your data is an essential data exploration practice. However, sometimes you need to provide a comprehensive view of data. So, you can filter data in the connected views in other browsers.

This picture shows that the left-side browser can control/filter data in 3 other browsers. This everyday use case explores master details data or other dependencies.

Import from excel or files

As part of WORKSHEETS Data Studio, you can leverage a low code programming language, JSPython or several UI tools to comprehensively import data from files or other systems and map them to the table fields.

Access your Database via REST API

Your SQL Database will be available through RESTfull API for better integration. In addition, we have open source libraries for JSPython, JavaScript, C# and Python.

SQL Data API is a central access point to control access to your databases through a REST API (HTTPS). Here is a SQL Data API JavaScript client or JSPython test harnesses

Role-Based Access Control

On top of existing SQL Security, we add another layer to securely control access to your database for authorised users or public access.

In the cloud or on-premise

The Enterprise version is available for your servers or in a private/public cloud.

One tool many databases

We are aiming to support all relational databases. We currently have the full support of MS SQL, AzureSQL and PostgreSQL (beta). But, we are going to add MySQL, Oracle etc.

SQL Data-Driven Workflow

Automate and streamline SQL Data-Driven Workflow and custom ETL pipelines.

Excel plug-in

WORKSHEETS Data Studio AddIn provides a flexible and controlled way to access and store your spreadsheet data to and from SQL Database

How can I use WORKSHEETS Data Studio?

Online (run.worksheet.systems). Just type in run.worksheet.systems, and you can access any private or public database. Works well with AzureSQL, AWS SQL, Google Cloud SQL
Chrome Extention. Same things just delivered as a chrome-extension
Enterprise. You can have your version of WORKSHEETS Data Studio behind your firewalls, comply with your security policies etc.

You can try Azure SQL Database free with an Azure free account

Conclusion

WORKSHEETS Data Studio simplifies your day-to-day data management tasks. And makes working with SQL databases with ease, control and confidence.

Originally published at https://www.worksheet.systems/blog/online-sql-client-for-low-code-data-management.

How to import JSON file into SQL Server Database

Pavlo Paska — Tue, 28 Jun 2022 08:19:12 +0000

The JSON format is very friendly for both people and for machines. In other words, it is very well structured for people to read as well as you can parse it quickly with any programming language.

However, all important information is better stored in relational SQL databases. In relational databases, all information is stored in tables, which consist of rows and columns. So, we will have to break down our JSON file into rows and columns.

Supporting Video tutorial

It is very easy to do with WORKSHEETS Data Studio. Just go to JSON editor and open the JSON file or paste a valid JSON data into the editor.

You will instantly see in a right-hand panel, that your JSON had been rendered into the data grid which has a table structure. Each element from an array is a row and each property is a column. Any nested objects are kept in a valid JSON format.

Please note. You have to have a valid SQL connection

So, we are ready to save this to the database. Press the Save To Database menu item. Choose:

SQL Connection, schema, tableName.
Make sure field mapping is correct
Choose the primary key

and you are ready to save.

We have 3 ways to save :

Upsert / Merge - So, it will insert or update rows in your table, based on a primary key(s). this is the default way and will work well in most cases.
Append - it will append only your data. A bit more performance optimized
Bulk Insert - it delivers the best performance. But, you will have to ensure data integrity
In a bit more advanced scenarios you may want to change the structure of your

Use JSPython to save JSON Data to SQL Database

If you need more flexibility and control, you can use JSPython to import your data. You can use SQL Data Api library to save. Also, you can save JSON to Worksheets Data Studio project and then import it into a jspy file for further processing

Open JSON file and process it

You can use openFileAsArray function

    openFileAsArray()

Transform JSON Data

You can save JSON data in the projects and then import it into JSPython

# transform object if needed
fileData = openFileAsArray()

return fileData.data.map(r =>
    r.fileName = fileData.fileName
    r.date = dateTime(r.date)
    r.avg = (r.high + r.low)/2
    return r
  )

Save Data To SQL

# Welcome to JSPython (https://jspython.dev)

from sql-data-api import sqlDataApi

fileData = openFileAsArray()

items = fileData.data
  .map(r =>
    r.fileName = fileData.fileName
    r.date = dateTime(r.date)
    r.avg = (r.high + r.low)/2
    return r
  )

sqlDataApi('public-data-connect').save('publicData.table2Ex', items)

Importing large JSON file

We do not recommend storing/loading large JSON files, because your browsers will run out of memory and crash. The previous article demonstrated how you can work with large JSON files. In this article, I will show you how you can import large JSON files into smaller chunks.

Here is a JSPython:

from sql-data-api import sqlDataApi

async def saveItemsToDatabase(items, fileName):
    #add fileName for each element
    items = items.map(r => Object.assign({fileName}, r))

    # save it to the database
    res = sqlDataApi('connectionName').save('schema.tableName',items)
    print(res)


    return openFileAsArray({
        chunkProcessor: saveItemsToDatabase
    }).data

As in a previous example, we use the openFileAsArray function. Where we define a chunkProcessor function, which uses sql-data-api to save items to the database. Meanwhile, as in this example, we are transforming elements and adding the fileName field.

Before running this code, you have to have SQL Connection defined and which has a table with a conforming structure. If you are running a large file, you can always open the first 1000 (or 10000) rows and use the Save To Database functionality to create a new table with all fields. Please make sure that varchar length and datatype can accommodate all rows in a file

WORKSHEETS Data Studio

WORKSHEETS Data Studio is a low-code data management studio which makes it easy to work with different kinds of files and we have straightforward processes to load JSON data into the database

How to open and analyse large JSON files online

Pavlo Paska — Tue, 07 Jun 2022 08:58:52 +0000

The main issue with large files is that we can’t load them entirely into the memory. It is easy to get out-of-memory error and crash. And obviously, all JSON parsers in all languages will run out-of-memory exception in no time. Any file larger than 100M is difficult and in most cases impossible to analyse.

JSON is a popular text-based format in the industry. Which represents a structured data-based object. It is commonly used for transmitting data in web applications (e.g., sending some data from the server to the client, so it can be displayed on a web page, or vice versa)

Also, it works well with JSV format — JSV Text Format (JSON + CSV) is a text-based format developed by the ServiceStack team and is optimized for both size and speed.

So, our goal here is to demonstrate to you how you can use WORKSHEETS Data Studio to process and analyse large JSON files straight in your browser (tested with JSON files up to 4G).

As I mentioned before, we can’t afford to load the entire file into the memory. So, how are we going to analyse then? There are a few tricks we can apply:

Limit result set to 100, 1000, or any other reasonable amount.
Get distinct values for one of the fields.
Apply filter criteria and reduce element count.
Transform each element and reduce the size you are loading.

All these actions you can do in your browser with Worksheets Data Studio and no installations are required. You can find it online: https://run.worksheet.systems or Chrome Extension

There are two ways you can process JSON files:

Use JSON editor to open/process files
Use JSPython. It is a more advanced way of processing files. Where you can program more logic into it. You can use the function openFileAsArray where you can choose file from the disk and parse data partially with all control you need. Function openFileAsArray comes with following parameters:
- take and skip — specify how many items you want to take or to skip. This will allow you to page big JSON file
- distinct — if the distinct field is defined, then the function will return unique values for this field
- containsText — filter results by searching text before parsing.
- filterText — a callback predicate function where you can have pre-parsed string and define filter criteria
- filter — a callback predicate function where you can define filter criteria with parsed element object
- map — a callback function where you can define/changes result items
- chunkSize — define a chunk size we are processing at a time. By default it is 64 * 1024 * 1024 (64K)
- chunkProcessor — a callback function where you can handle a parsed chunk of items. If this function is defined, then we will not return items. You will have to process a batch of items yourself. In one of the use cases, you can save a batch of items to the database.

Let's review all 4 use cases and I will demonstrate all available approaches:

Limit elements count

A good starting point is to look for a small number of elements, lets's say you want to see only 1000 elements and figure out the element’s structure and decide your future steps for analysis:

Use JSON editor in Worksheets Data Studio

Open new blank JSON tab

In the top right corner, you will notice the button Open File and Open file partially. Where the first button will open and load the entire JSON/JSV/CSV file into JSON Editor. And button Open file partially will open the following dialog, which will allow you to work with large files

If you press Open File button. You will be able to see the first 1000 elements from your JSON file. This is very helpful to analyze file structure and decide on further analysis actions

Use JSPython

Alternatively, you can use JSPython editor and programmatically handle results. FunctionopenFileAsArray will open the same dialog and will return processed elements results for further data manipulations in JSPython.

Here is an example

# Welcome to JSPython (https://jspython.dev) 
data = openFileAsArray({ 
   take: 1000 
   #skip: 1000 
}).data 
# work with data array here 
return data

Get Distinct values

After you’ve seen a file structure and the top 1000 elements in your JSON file. Next, you would be probably more interested to see unique values for some of the fields. Let’s say, in our example, you would be interested to see the unique Product_ID in the file

Or you can achieve the same result with JSPython code

Apply filter criteria

And now, when you’ve seen a chunk of your JSON file and analyzed unique values from this file. You should know enough to filter and get whatever is needed for your analysis.
Let's say you want to see only elements that contain the text InterestRate:CrossCurrency:FixedFloat.

Then you can specify in the dialog:

Or with JSPython you can define a predicate function with more advanced filter criteria. you can add extra filter criteria e.g. Action == ‘CANCEL’

Transform results

One other way of reducing memory footprint is to remove some of the fields for each element. Especially, if some of the fields are not important. This option is available only with JSPython code

Here is JSPython example with filter callback and transformation map

openFileAsArray({ 
  filter: r=> 
     r.Product_ID == 'InterestRate:CrossCurrency:FixedFloat' 
       and r.Action == 'CANCEL',
  map: r => return { 
     id: r.Dissemination_ID,
     effectiveDate: dateTime(r.Effective_Date.substring(0, 10)),
     notional: r.Notional_Amount_1,
     notionalCcy: r.Notional_Currency_1 
    }
}).data

and eventually, results will look like

Video tutorial

https://youtu.be/j1ro4VJZoDA

Conclusion

WORKSHEETS Data Studio simplifies working with large JSON files or even, sometimes, is the only option to view and analyse large JSON file. However, the best way to analyze big files will be to load the entire file into an SQL database and then use SQL queries to analyze much more effectively. This is something we will show you how to do in the next tutorial.

Originally published at https://www.worksheet.systems

Why we introduced our new coding language - JSPython?

Pavlo Paska — Sat, 07 Aug 2021 09:26:45 +0000

I’ve worked with a dozen programming languages during my career in software development. Anything from Basic, Pascal, C* to Python, JavaScript, and more. And here we are, fast forward 25 years and I had no other option than to invent another coding language. With all questions asking, ‘why?’

Do we want to learn another programming language? - No, Python is the most popular language in the world

Do we have time to rebuild libraries for all the needs? - No, JavaScript has huge communities and a huge EcoSystem around it.

We had a requirement to allow users to define custom logic at runtime safely. We tried a JSON-based configuration, but, as complexities grew, that configuration became unmaintainable. Even UI-based drag’n drop interfaces become messy for bigger and more complex logic. And after a few failed attempts I decided to build my interpreter and introduce a new coding language to the world. And syntax-wise I choose to start with Python as it is the most familiar syntax among our target audience.

JSPython is a python-like syntax interpreter implemented with javascript that runs entirely in the web browser and/or in the NodeJS environment. It does not transpile/compile your code into JavaScript, instead, it provides an interactive interpreter that reads Python-like code and carries out its instructions. With JSPython you should be able to safely interact with any JavaScript libraries or API’s with the popular Python-like syntax.

With JSPython you can use any JavaScript library with Python-like syntax. You can use standard Javascript objects, arrays, callback functions, as well as build your custom logic with JavaScript and expose it to JSPython users

Main advantages of JSPython

Safe scripting environment. You have full control over what functions and libraries you expose to JSPython and allow users to use.
Works in a browser as well as offline on the server-side. You can integrate it easily with your web app in the browser and/or run jspy files offline with jspython-cli in the NodeJS environment
The python-like syntax. Most of the language features (syntax, indentation, and expressions) are mirrored from Python. Also, added a few extras like Multiline lambdas, Null coalescing, single line conditions, etc
Utilize the entire JavaScript ecosystem. Leverage a massive JavaScript ecosystem with Python scripting. You should be able to import and invoke any JS function with the Python syntax
Rapid Application Development. The seamless interaction between Python and JavaScript allows developers to enjoy the best of two worlds. And deliver business solutions much quicker and more reliably.

Where to start?

JSPython is an open source JavaScript library with a permissive BSD 3-Clause License. Published to npm as jspython-interpreter

Run JSPython in your JavaScript App

npm install jspython-interpreter

jsPython()
  .evaluate('print("Hello World!")')            
  .then(
    r => console.log("Result => ", r),            
    e => console.log("Error => ", error)            
  )

Run JSPython in NodeJS with JSPython-CLI

Use JSPython-cli command line tool what allows you to run JSPython in NodeJS environment

jspython --file=path/to/jspython/file --param1=value --param

Any similar projects around?

Obviously, before committing to such a big endeavour I’ve looked around for existing libraries. The closest library is Skulpt but their API isn’t flexible enough and we couldn’t utilize our JavaScript libraries

Performance

Many people are asking about performance and maturity of the project. How does it compare to Python performance-wise?

Well, it is very comparable to CPython, mainly because CPython is quite slow :), while Google’s V8 JavaScript engine is extremely optimized and performant. You will find that NodeJS will significantly outperform any CPython project. So, I can confidently say that JSPython is fast enough to serve most of your custom workflow and behaviour logic. And is already used in several performance critical production systems.

Coding playground

Check out our basic playground https://jspython.dev/playground

Or more advanced examples build with WORKSHEETS Data Studio

sample code

# initial array
nums = [1, 2]
""" 
You can use all standard JavaScript functions 
(https://www.w3schools.com/jsref/jsref_obj_array.asp). 
"""

# add more numbers
for i in range(3,10):
    nums.push(i)

# `filter` will leave only odd numbers
# `map` will create an object
# y will be an array of objects transformed from odd numbers
y = nums
    .filter(n => n % 2)
    .map((n, i) =>
        newItem = {
            index: i,
            element: n,
            square: Math.pow(n, 2),
            cube: Math.pow(n, 3)
        }
        return newItem
    )     


print('Prints to console =>', y)

# last statement will be a result
return y

Worksheet Systems

JSPython was build to power our low code data management platform - Worksheet Systems. Which allows anybody to build database applications with ease, control and confidence. JSPython is used as a primary low code language to define UI behaviours, validations, build data transformational pipelines and much more.

However, it is open source project with a permissive BSD 3-Clause License. So, feel free to adopt it into your own projects.