DEV Community: Olajide Olaniyan

Sampling in PostgreSQL with BERNOULLI Function

Olajide Olaniyan — Tue, 18 Jul 2023 16:09:02 +0000

Introduction:

As the volume of data continues to grow exponentially, efficiently analyzing large datasets has become a critical challenge for data engineers and analysts.In a current project which uses a postgreSQL database, I recently encountered a data sampling issue, but not in my role as a data analyst, but rather as a developer trying to create an algorithm that uses a portion of a sizable dataset per iteration.

In the world of relational databases, PostgreSQL stands out as a powerful and flexible option. One of its lesser-known yet immensely useful features is sampling, which allows users to work with representative subsets of data for faster analysis. In this blog post, we will explore the BERNOULLI sampling function in PostgreSQL, its benefits, and how it can be utilized to enhance query performance.

What is Sampling in PostgreSQL?

In the context of databases, sampling is the process of selecting a random selection of data for analysis from a larger dataset. It is especially helpful when working with large tables, where it may be time-consuming and resource-intensive to execute complicated queries on the full dataset. The BERNOULLI sampling function in PostgreSQL enables users to choose random rows from a table based on a given probability.

Understanding the BERNOULLI Function:

The BERNOULLI sampling function in PostgreSQL is a powerful tool to perform random sampling on a table. Its syntax is simple:

SELECT * FROM table_name TABLESAMPLE BERNOULLI(sample_percentage);

Here, "table_name" represents the name of the table from which you want to sample, and "sample_percentage" is the percentage of the table's rows you wish to include in the sample. The sample_percentage value is a floating-point number between 0 and 100, representing the percentage of the total rows to be included in the sample.

Benefits of Using BERNOULLI Sampling:

Improved Query Performance: A smaller, more representative sample of the data can be used to run queries much faster. When working with huge tables, where conventional searches could be resource-intensive, this is very useful.
Efficient Data Exploration: Without having to examine the complete dataset, sampling enables data analysts to quickly grasp the properties and trends of the dataset. For data exploration and hypothesis testing, it's a useful step.
Reduced Overhead: When dealing with real-time or near-real-time data analysis, generating a sample can provide quick insights while reducing computational overhead.
Statistical Validity: The BERNOULLI sampling technique guarantees that each table row has an equal probability of being included in the sample, producing findings that are statistically valid.

Utilizing BERNOULLI Sampling in Practice:

Let's walk through a practical example to illustrate how BERNOULLI sampling can be used in PostgreSQL.

Suppose we have a massive sales table with millions of records and want to perform an exploratory analysis on a random subset of the data:

-- Select a random 10% sample from the sales table
SELECT * FROM sales TABLESAMPLE BERNOULLI(10);

By running this query, we can efficiently retrieve a representative 10% sample of the sales data without loading the entire table, saving time and resources.

For my project, the requirement was slightly different, I must sample the full dataset rather than simply a subset of it based on a particular criterion. For each iteration, 3 random samples from the entire dataset should be returned

-- Select entire dataset from the garments table
 select * from  garments  tablesample BERNOULLI(1) where $1=$2 limit 3

Conclusion:

By using random subsets of data, the BERNOULLI sampling function in PostgreSQL enables users to evaluate big datasets quickly and effectively. This feature speeds up hypothesis testing and data exploration while simultaneously improving query performance. Data engineers and analysts can extract valuable insights from huge datasets without sacrificing computing effectiveness or statistical validity by using BERNOULLI sampling in PostgreSQL.

The BERNOULLI function in PostgreSQL makes sampling, a useful tool in the database toolbox, accessible to users that want to streamline their data analysis operations. This capability will likely become essential for anyone looking to make educated decisions and extract valuable information from their PostgreSQL databases as the volume and complexity of data continue to increase.

Streaming Currency and Cryptocurrency Data Using WebSockets and Node.js with ReactJs client

Olajide Olaniyan — Mon, 10 Jul 2023 12:49:14 +0000

Introduction:

The world of finance operates at lightning speed, and real-time data is crucial for making informed decisions. Traditionally, retrieving up-to-date currency and cryptocurrency information required frequent API calls, which could be inefficient and resource-intensive. However, with the advent of WebSockets and the power of Node.js, developers now have a more efficient solution. In this blog post, we will explore how to use WebSockets and Node.js to stream currency and cryptocurrency data in real-time, ensuring that you have the most accurate information at your fingertips.

Prerequisites:

To follow along with this tutorial, it is recommended to have a basic understanding of Node.js and JavaScript. Familiarity with WebSockets will also be beneficial but not required.

Setting Up the Project:

Ensure that you have Node.js and npm (Node Package Manager) installed on your machine.
Create a new directory for your project and navigate to it using the terminal or command prompt.
Initialize a new Node.js project by running the command: npm init -y.
Install the required dependencies:
- Express.js: npm install express
- WebSocket library: npm install ws
- Axios (for making API requests): npm install axios

Server-side Implementation:

Create a new file called server.js and require the necessary modules.

You should have a server file that looks like the code below, please not that implementation may vary but the underlining principle is the same.

const http = require("http");



const port = normalizePort(process.env.PORT || "3000");
app.set("port", port);


const server = http.createServer(app);



server.listen(port);
server.on("error", onError);
server.on("listening", onListening);



function normalizePort(val) {
  const port = parseInt(val, 10);

  if (isNaN(port)) {
    // named pipe
    return val;
  }

  if (port >= 0) {
    // port number
    return port;
  }

  return false;
}



function onError(error) {
  if (error.syscall !== "listen") {
    throw error;
  }

  const bind = typeof port === "string" ? "Pipe " + port : "Port " + port;

  // handle specific listen errors with friendly messages
  switch (error.code) {
    case "EACCES":
      console.error(bind + " requires elevated privileges");
      process.exit(1);
      break;
    case "EADDRINUSE":
      console.error(bind + " is already in use");
      process.exit(1);
      break;
    default:
      throw error;
  }
}


function onListening() {
  const addr = server.address();
  const bind = typeof addr === "string" ? "pipe " + addr : "port " + addr.port;
  debug("Listening on " + bind);
}

Set up an Express.js server _app.js to handle the rest of server logic.
Initialize a WebSocket server using the 'ws' library and listen for incoming connections.

Basically, the whole implementation should look like the code below using ws modules

const WebSocket = require('ws');

const ws = new WebSocket('wss://stream.binance.com:9443/ws/btcusdt@trade');

ws.on('open', () => {
  console.log('WebSocket connection established');
});

ws.on('message', (data) => {
  const trade = JSON.parse(data);
  console.log(`Trade executed at price ${trade.p} with quantity ${trade.q}`);
});

ws.on('close', () => {
  console.log('WebSocket connection closed');
});

Fetch currency and cryptocurrency data from an API at regular intervals. The basic code structure above ☝️ will be used to fetch cryptocurrency data at stream intervals.

wsServer.on("request", function (request) {
  const connection = request.accept(null, request.origin);
  console.log(new Date() + " Connection accepted.");


  connection.on("message", function (message) {
    if (message.type === "utf8") {
      console.log(new Date() + "Message received: " + message.utf8Data);

      const trade = JSON.parse(data);
      console.log(`Trade executed at price ${trade.p} with quantity ${trade.q}`);
      connection.send(JSON.stringify(data))

      //implementation below is simply an additional code
      //you can fetch from mongodb streams as below
      Exchange.watch().on("change", (data) =>
      connection.send(JSON.stringify(data.fullDocument))
    );

    }

    else if (message.type === 'binary') {
      console.log('Received Binary Message of ' + message.binaryData.length + ' bytes');
    }
  });

  connection.on("close", function (reasonCode, description) {
    console.log(
      new Date() +
        " Peer " +
        connection.remoteAddress + " disconnected."
    );
  });
});

Send the updated data to connected WebSocket clients whenever it changes as shown in the code above ☝️.

Client-side Implementation:

React

Create the React app.

React.js (if not already installed): npx create-react-app client

Modify the 'App.js' file to establish a WebSocket connection with the server.
Implement event handlers to process incoming data from the WebSocket and update the React state accordingly.
Design and render components to display the real-time currency and cryptocurrency data.

const [ws, setWs] = React.use useState(new WebSocket("wss://...server"));

get streams of data from the server using the useEffect hook

useEffect(() => {
    //setup websocket
    ws.onopen = (event) => {
      //check to confirm connection from both
      //you can inspect websockets using a chrome add-on
      //The message below should be displayed when you client app is connected
      ws.send("Message sent from React client");
    };

    // recieve stream data, our implementation uses a state function setData
    ws.onmessage = (stream) => {
      setTdata([...tData, JSON.parse(stream.data)]);
    };

    return () => {
      ws.onclose = () => {
        //reconnect to server ever 1000 * 10 ms
        console.log("socket close : will reconnect in " + 1000 * 10);
        setWatchState(false);
        setTimeout(
          () => setWs(new WebSocket("wss://...server")),
          1000 * 10
        );
      };
    };
  }, []);

Simply used the received data as you wish in your React.Js app.

Conclusion:

In this blog post, we explored how to use WebSockets and Node.js to stream currency and cryptocurrency data in real-time. By leveraging the power of WebSockets, we can establish a persistent connection between the server and the client, eliminating the need for frequent API calls. This approach ensures that we have the most accurate and up-to-date data available at all times. Whether you are building a financial application, a trading platform, or simply want to display live data on your website, WebSockets and Node.js provide an efficient solution. Take the code provided, experiment, and create dynamic and interactive applications that keep your users informed in real-time. Happy coding!

Easy StackOverflow(ing) with CaptainStack and VsCode

Olajide Olaniyan — Mon, 10 Apr 2023 12:48:05 +0000

If you're a developer who uses Visual Studio Code, then you know how powerful and versatile this code editor can be. And with the right set of extensions, you can take your coding experience to the next level.

The use of AI in programming has opened up a whole new world of possibilities, and we can expect to see even more innovations in the future. Using AI with your dev work is obviously an interesting prospect. The new developers will be those that have learnt how to use AI with their knowledge to performing interesting task.

If you do not feel buoyant enough to get on the GitHub co-pilot train, there are few tools that are equally useful.

I have been testing a few and found CaptainStack to be an interesting tool, however CaptainStack instead of using AI, only sends search queries to Google, gets answers from StackOverflow and Github Gist, and autocompletes them.

You can make quick searches from StackOverflow and Github Gist.

you make a simple query by using the

//find

keyphrase and ending the query with a period

(.)