No data, no AI: Data is essential to solving our toughest challenges in business, science, and society

Datarade

datarade

By: Jon Keegan and Alfred Ng A huge but little-known industry has cropped up around monetizing people’s movements. Companies that you likely have never heard of are hawking access to the location history on your mobile phone. An estimated , the location data industry has many players: collectors, aggregators, marketplaces, and location intelligence firms, all of which boast about the scale and precision of the data that they’ve amassed. $12 billion market Location firm Near describes itself as “The World's Largest Dataset of People's Behavior in the Real-World,” with data representing “1.6B people across 44 countries.” Mobilewalla boasts “40+ Countries, 1.9B+ Devices, 50B Mobile Signals Daily, 5+ Years of Data.” X-Mode’s website claims its data covers “25%+ of the Adult U.S. population monthly.” In an effort to shed light on this little-monitored industry, The Markup has identified 47 companies that harvest, sell, or trade in mobile phone location data. While hardly comprehensive, the list begins to paint a picture of the interconnected players that do everything from providing code to app developers to monetize user data to offering analytics from “ ” and on hundreds of millions of people. Six companies claimed more than a billion devices in their data, and at least four claimed their data was the “most accurate” in the industry. 1.9 billion devices access to datasets “There isn't a lot of transparency and there is a really, really complex shadowy web of interactions between these companies that’s hard to untangle,” Justin Sherman, a cyber policy fellow at the Duke Tech Policy Lab, said. “They operate on the fact that the general public and people in Washington and other regulatory centers aren't paying attention to what they're doing.” Occasionally, stories illuminate just how invasive this industry can be. In 2020, Motherboard reported that a company that collects location data through apps, was collecting data from Muslim prayer apps and selling it to military contractors. X-Mode, The Wall Street Journal also reported in 2020 that for immigration enforcement. Venntel, a location data provider, was selling location data to federal agencies A Catholic news outlet also </a> to out a priest who had frequented gay bars, though it’s still unknown what company sold that information. used location data from a data vendor Many firms promise that privacy is at the center of their businesses and that they’re careful to never sell information that can be traced back to a person. But researchers studying anonymized location data just how misleading that claim can be. have shown The truth is, it’s hard to know all the ways in which your movements are being tracked and traded. Companies often reveal little about what apps serve as the sources of data they collect, what exactly that data consists of, and how far it travels. To piece together a picture of the ecosystem, The Markup reviewed the websites and marketing language of each of the 47 companies we identified as operating in the location data industry, as well as any information they revealed about how the data got to them. . See our methodology here How the Data Leaves Your Phone Most times, the location data pipeline starts off in your hands, when an app sends a notification asking for permission to access your location data. Apps have all kinds of reasons for using your location. Map apps need to know where you are in order to give you directions to where you’re going. A weather, waves, or wind app checks your location to give you relevant meteorological information. A video streaming app checks where you are to ensure you’re in a country where it’s licensed to stream certain shows. But unbeknownst to most users, some of those apps sell or share location data about their users with companies that analyze the data and sell their insights, like Advan Research. Other companies, like Adsquare, buy or obtain location data from apps for the purpose of aggregating it with other data sources. Companies like real estate firms, hedge funds and retail businesses might then turn and use the data for their own advertising, analytics, investment strategy, or marketing purposes. Serge Egelman, a researcher at UC Berkeley’s ​​International Computer Science Institute and CTO of AppCensus, who has , said it’s hard to tell which apps on your phone simply use the data for their own functional purposes and which ones release your data into the economic ether. researched sensitive data permissions on mobile apps “When the app asks for location, in the moment, because maybe you click the button to find stuff near you and you get a permission dialog, you might reasonably infer that ‘Oh, that's to service that request to provide that functionality,’ but there's no guarantee of that,” Egelman said. “And there's certainly usually never a disclosure that says that the data is going to be limited to that purpose.” Companies that trade in this data are reluctant to share which apps they get data from. The Markup asked spokespeople from . all the companies on our list</a> where they get the location data they obtain Companies like Adsquare and Cuebiq told The Markup that they don’t publicly disclose what apps they get location data from to keep a competitive advantage but maintained that their process of obtaining location data was transparent and with clear consent from app users. “It is all extremely transparent,” said Bill Daddi, a spokesperson for Cuebiq. He added that consumers must know what the apps are doing with their data because so few consent to share it. “The opt-in rates clearly confirm that the users are fully aware of what is happening because the opt-in rates can be as low as less than 20%, depending on the app,” Daddi said in an email. Yiannis Tsiounis, the CEO of the location analytics firm Advan Research, said his company buys from location data aggregators, who collect the data from thousands of apps—but would not say which ones. Tsiounis said the apps he works with do explicitly say that they share location data with third parties somewhere in the privacy policies, though he acknowledged that most people don’t read privacy policies. “There’s only so much you can squeeze into the notification message. You get one line, right? So you can’t say all of that in the notification message,” Tsiounis said. “You only get to explain to the user, ‘I need your location data for X, Y, and Z.’ What you have to do is, there has to be a link to the privacy policy.” Only one company spokesperson, Foursquare’s Ashley Dawkins, actually named any specific apps—Foursquare's own products, like Swarm, CityGuide, and Rewards—as sources for its location data trove. But Foursquare also produces a free software development kit (SDK)—a set of prebuilt tools developers can use in their own apps—that can potentially track location through any app that uses it. is used in apps like , a service that compares prices at nearby gas stations, , a shopping app for coupons, and , another location-based discount app. Foursquare’s Pilgrim SDK GasBuddy Flipp Checkout 51 GasBuddy, Flipp, and Checkout 51 didn’t respond to requests for comment. A search on l a site that analyzes and tracks SDKs in apps, found Foursquare’s Pilgrim SDK in 26 Android apps. Mighty Signa While not every app with Foursquare’s SDK sends location data back to the company, the privacy policies for Flipp, Checkout 51, and GasBuddy all disclose that they share location data with the company. Foursquare’s method of obtaining location data through an embedded SDK is a common practice. Of the 47 companies that The Markup identified, 12 of them advertised SDKs to app developers that could send them location data in exchange for money or services. i says in its marketing that it does foot traffic analysis and that its SDK is installed in more than 500 apps and has insights on more than 20 million devices. Placer.a “We partner with mobile apps providing location services and receive anonymized aggregated data. Very critically, all data is anonymized and stripped of personal identifiers before it reaches us,” Ethan Chernofsky, ’s vice president of marketing, said in an email. Placer.ai Into the Location Data Marketplace Once a person’s location data has been collected from an app and it has entered the location data marketplace, it can be sold over and over again, from the data providers to an aggregator that resells data from multiple sources. It could end up in the hands of a “location intelligence” firm that uses the raw data to analyze foot traffic for retail shopping areas and the demographics associated with its visitors. Or with a hedge fund that wants insights on how many people are going to a certain store. “There are the data aggregators that collect the data from multiple applications and sell in bulk. And then there are analytics companies which buy data either from aggregators or from applications and perform the analytics,” said Tsiounis of Advan Research. “And everybody sells to everybody else.” Some data marketplaces are part of well-known companies, like or , which sell all types of data, not just location data. Oracle boasts its listing as the “world’s largest third-party data marketplace” for targeted advertising, while Amazon claims to “make it easy to find, subscribe to, and use third-party data in the cloud.” Both marketplaces feature listings for several of the location data companies that we examined. Amazon’s AWS Data Exchange Oracle’s Data Marketplace Amazon spokesperson Claude Shy said that data providers have to explain how they gain consent for data and how they monitor people using the data they purchase. “Only qualified data providers will have access to the AWS Data Exchange. Potential data providers are put through a rigorous application process,” Shy said. Oracle declined to comment. Other companies, like Narrative, say they are simply connecting data buyers and sellers by providing a platform. lists location data providers like SafeGraph and Complementics among its 17 providers with more than two billion mobile advertising IDs to buy from. Narrative’s website, for instance But Narrative CEO Nick Jordan said the company doesn’t even look at the data itself. “There’s a number of companies that are using our platform to acquire and/or monetize geolocation data, but we actually don’t have any rights to the data,” he said. “We’re not buying it, we’re not selling it.” To give a sense of how massive the industry is, Amass Insights has 320 location data providers listed on its directory, Jordan Hauer, the company’s CEO, said. While the company doesn’t directly collect or sell any of the data, hedge funds will pay it to guide them through the myriad of location data companies, he said. “The most inefficient part of the whole process is actually not delivering the data,” Hauer said. “It’s actually finding what you’re looking for and making sure that it’s compliant, making sure that it has value and that it is exactly what the provider says it is.” Oh, the Places Your Data Will Go There are a whole slew of potential buyers for location data: investors looking for intel on market trends or what their competitors are up to, political campaigns, stores keeping tabs on customers, and law enforcement agencies, among others. Data from location intelligence firm Thasos Group has been used to measure the l . number of workers pulling extra shifts</a> at Tesla plants. Political campaigns on both sides of the aisle have also used ocation data from people who were at rallies</a> for targeted advertising Fast food restaurants and other businesses have been known to buy location data for advertising purposes down to a person’s steps. For example, in 2018, Burger King ran a promotion in which, if a customer’s phone was within , the Burger King app would let the user buy a Whopper for one cent. 600 feet of a McDonalds The Wall Street Journal and Motherboard have also written extensively about how federal agencies including the , , and bought location data from companies tracking phones. Internal Revenue Service Customs and Border Protection the U.S. military Of the location data firms The Markup examined, the offerings are diverse. Advan Research, for instance, uses historical location data to tell its customers, largely retail businesses or their private equity firm owners, where their visitors came from, and makes guesses about their income, race, and interests based on where they’ve been. “For example, we know that the average income in this neighborhood by census data is $50,000. But then there are two devices—one went to Dollar General, McDonald’s, and Walmart, and the other went to a BMW dealer and Tiffany’s ... so they probably make more money,” Advan Research’s Tsiounis said. Others combine the location data they obtain with other pieces of data gathered from your online activities. Complementics, which boasts data on “more than a billion mobile device IDs,” offers for mobile ad targeting. location data in tandem with cross-device data The prices can be steep. Outlogic (formerly known as X-Mode) offers a license for a titled “Cyber Security Location data” on Datarade for $240,000 per year. The listing says “Outlogic’s accurate and granular location data is collected directly from a mobile device’s GPS.” location dataset At the moment, there are few if any rules limiting who can buy your data. Sherman, of the Duke Tech Policy Lab, </a> finding that data brokers were advertising location information on people based on their political beliefs, as well as data on U.S. government employees and military personnel. published a report in August “There is virtually nothing in U.S. law preventing an American company from selling data on two million service members, let's say, to some Russian company that's just a front for the Russian government,” Sherman said. Existing privacy laws in the U.S., like California’s Consumer Privacy Act, do not limit who can purchase data, though California residents can request that their data not be “sold”—which can be n. Instead, the law focuses on allowing people to opt out of sharing their location in the first place. a tricky definitio The European Union’s General Data Protection Regulation has stricter requirements for when their data is being processed or transferred. notifying users But Ashkan Soltani, a privacy expert and former chief technologist for the Federal Trade Commission, said it’s unrealistic to expect customers to hunt down companies and insist they delete their personal data. “We know in practice that consumers don't take action,” he said. “It's incredibly taxing to opt out of hundreds of data brokers you've never even heard of." Companies like Apple and Google, who control access to the app stores, are in the best position to control the location data market, AppCensus’s Egelman said. “The real danger is the app gets booted from the Google Play store or the iOS app store,” he said.” As a result, your company loses money.” </a> from using location reporting SDKs from several data companies. Google and Apple both recently banned app developers , however, that the companies’ SDKs were still making their way into Google’s app store.  Apple didn’t respond to a request for comment. Researchers found "The Google Play team is always working to strengthen privacy protections through both product and policy improvements. When we find apps or SDK providers that violate our policies, we take action,” Google spokesperson Scott Westover said in an email. Digital privacy has been a key policy issue for U.S. senator Ron Wyden, a Democrat from Oregon, who told The Markup that the big app stores needed to do more. “This is the right move by Google, but they and Apple need to do more than play whack-a-mole with apps that sell Americans’ location information. These companies need a real plan to protect users’ privacy and safety from these malicious apps,” Wyden said in an email. This article was license. originally published on The Markup</a> and was republished under the Creative Commons Attribution-NonCommercial-NoDerivatives

Companies Pay Big Money to Have the Location Data From Your Phone

Most of the time when I’m developing applications, I default to using some form of SQL. Whether it’s a simple application using SQlite or something a bit more robust that I plan to scale out with MySQL, it’s inevitably some flavor of SQL. However, when starting new projects over the past few years I’ve been trying to make sure I have experience with document databases and have spent more time using in C#. MongoDB In this article, I’ll guide you through some basics for being able to insert data using MongoDB in C#. We’ll keep it light and to the point, so let’s dive into a brief overview of MongoDB in C# followed by some code examples in C#! Overview of MongoDB in C# MongoDB is a widely used NoSQL database that provides a flexible and scalable solution for data storage. One of the key benefits of MongoDB is its document-oriented nature so instead of using tables with fixed schemas like in traditional relational databases, MongoDB stores data in collections of JSON-like documents. This can allow for a more flexible and dynamic data model, especially in scenarios where data structures can vary. In C#, you can seamlessly integrate MongoDB into your applications because MongoDB provides a native driver for C#. This simplifies the process of interacting with the database and provides a comfortable development experience for C# developers. Well, mostly. If you’re used to using SQL connections, SQL command objects, and classes to access your data, it’s a little bit more cumbersome. But this is coming from the perspective of someone who prefers to write raw SQL queries in their data access layer. I suspect if you didn’t have this bias then working with their API, especially for filtering, would feel pretty intuitive. DataReader One big thing you’ll want to keep in mind if you’re coming from a relational database background is understanding the primary differences between relational and document databases. In the former, we generally denormalize data across tables and, in the latter, we write the data as a document that we’d like to read back without having to join. These types of databases aren’t designed to join data like we do with relational databases, so it’s something to be aware of! Follow Along with MongoDB in C# Content I always try to make sure I provide multiple modes of content when I’m able to. Below, you’ll find a YouTube video walking through examples of MongoDB in C# for inserting records: https://youtu.be/0fB9qg-oR04?embedable=true Of course, if you’d rather play around with some working code (aside from ensuring you have your own database), you can find the example code on my . You should be able to clone it, set up your connection string properly, change your database and collection names as needed, and then rock and roll! Dev Leader GitHub repository here Inserting Documents into MongoDB in C# In this section, I’ll explain how to insert documents into MongoDB using C#. We’ll look at code examples using the and methods. We’ll see these methods in their synchronous and asynchronous forms! Remember, you will need to ensure you have the “MongoDB.Driver” NuGet package installed in your project for the following code examples to work. InsertOne InsertMany Using the InsertOne and InsertOneAsync Methods To insert a single document into a MongoDB collection synchronously, use the method. This method is straightforward and is executed immediately, blocking the current thread until the operation completes. Here’s a basic example: InsertOne using MongoDB.Bson;
using MongoDB.Driver;

// Connect to MongoDB instance
var client = new MongoClient("mongodb://localhost:27017");

// Select database
var database = client.GetDatabase("testDatabase");

// Select collection (sort of like a SQL "table")
var collection = database.GetCollection<BsonDocument>("myCollection");

var document = new BsonDocument
{
    { "name", "John" },
    { "age", 30 }
};

// Insert document into collection
collection.InsertOne(document); In this example: We first establish a connection to the MongoDB instance and select our database and collection. Then, we create a object, which represents the document we want to insert. BsonDocument Finally, we call on our collection object, passing in the document. This inserts the document into the specified collection. InsertOne For asynchronous operations, you can use . This method is especially useful in applications that require non-blocking operations, such as web applications or services, to maintain responsiveness. Here’s how to use it: InsertOneAsync using MongoDB.Bson;
using MongoDB.Driver;
using System.Threading.Tasks;

// just like the former example
var client = new MongoClient("mongodb://localhost:27017");
var database = client.GetDatabase("testDatabase");
var collection = database.GetCollection<BsonDocument>("myCollection");

var document = new BsonDocument
{
    { "name", "Jane" },
    { "age", 28 }
};

// Asynchronously insert document
await collection.InsertOneAsync(document, cancellationToken: CancellationToken.None); In this asynchronous example: The setup is similar to the synchronous version, but we use with . This tells the program to continue with other work that doesn’t depend on the completion of the insert operation. await InsertOneAsync Take note that you can pass in a cancellation token here which is a best practice for async code Note that both and methods throw exceptions if the insert operation fails, so you should use try-catch blocks to handle potential errors gracefully. InsertOne InsertOneAsync Using the InsertMany and InsertManyAsync Methods The method synchronously inserts a list of document objects into the specified collection. This is useful when you have multiple documents ready to be stored and want to execute the operation in a single call. Here’s an example: InsertMany using MongoDB.Bson;
using MongoDB.Driver;
using System.Threading.Tasks;

// just like the former example
var client = new MongoClient("mongodb://localhost:27017");
var database = client.GetDatabase("testDatabase");
var collection = database.GetCollection<BsonDocument>("myCollection");

var documents = new List<BsonDocument>
{
    new BsonDocument("name", "John").Add("age", 30),
    new BsonDocument("name", "Jane").Add("age", 25),
    new BsonDocument("name", "Doe").Add("age", 28)
};

collection.InsertMany(documents); In this example, we first get a reference to our collection. Then, we of objects, each representing a document to insert. Finally, we call with our list of documents. This method will insert all documents in the list into the MongoDB collection in one operation. create a list BsonDocument InsertMany For asynchronous operations, particularly useful in applications where responsiveness is crucial or when dealing with I/O-bound tasks, you can use the method. This method works similarly to , but it performs the operation asynchronously. Here’s an example: InsertManyAsync InsertMany using MongoDB.Bson;
using MongoDB.Driver;
using System.Threading.Tasks;

// just like the former example
var client = new MongoClient("mongodb://localhost:27017");
var database = client.GetDatabase("testDatabase");
var collection = database.GetCollection<BsonDocument>("myCollection");

var documents = new List<BsonDocument>
{
    new BsonDocument("name", "John").Add("age", 30),
    new BsonDocument("name", "Jane").Add("age", 25),
    new BsonDocument("name", "Doe").Add("age", 28)
};

await collection.InsertManyAsync(documents); In this asynchronous version, we use before calling , ensuring that the operation completes before moving on to the next line of code. This is especially important in web applications or services where blocking the main thread could lead to poor performance or user experience. await InsertManyAsync Best Practices for Efficiently Inserting Data This article is focused on the API usage of inserting data into MongoDB using C#, but I wanted to touch on a few other points. To improve the efficiency of data insertion in MongoDB, you can consider the following best practices: Batch Inserts: Instead of inserting one document at a time, you can batch multiple documents together and insert them in a single operation using InsertMany. This reduces the overhead of multiple round-trips to the database. Use Indexes: Indexes can significantly improve the insert performance by speeding up the search for the correct position to insert the new document. Ensure that you have appropriate indexes defined on the fields commonly used for insertion. Consider Sharding: If you have a large amount of data to insert, sharding can distribute the data across multiple servers, improving insertion performance. Write Concern: The default write concern in MongoDB is Acknowledged, which waits for an acknowledgment from the server. If you are performing bulk insertions and do not require immediate acknowledgment, you can set a lower write concern to improve performance. Consider Asynchronous Operations: Asynchronous operations can improve the responsiveness of your application by allowing multiple insert operations to be executed concurrently. Aside from asynchronous versions of these APIs which were mentioned earlier, I won’t be covering these other topics in more detail in this article. Stay tuned for upcoming articles where I cover these topics along with other functionality, such as updating documents in MongoDB and deleting records! Wrapping Up MongoDB in C# To recap, in this article, we explored the topic of writing data to MongoDB in C# using InsertOne and InsertMany APIs. We saw both the synchronous and asynchronous versions of these APIs in functional code examples. Some of the key takeaways: Understanding relational database vs document database characteristics Best practice considerations for performance Synchronous vs asynchronous APIs for inserting data to MongoDB in C# You should continuously improve your skills and stay updated with the latest technologies! If you found this useful and you’re looking for more learning opportunities, consider and check out my ! subscribing to my free weekly software engineering newsletter free videos on YouTube

Simplified Guide For Inserting Data Into MongoDB Using C#

In this article, I aim to share my experience standardizing the ETL process within frameworks like Spark and Flink, particularly for those not using data processing frameworks like dbt. Context Data processing is a comprehensive field that encompasses the regular processing of substantial information volumes in both batch and stream modes. These procedures, often referred to as ETL processes, are developed using a variety of programming languages and frameworks. Typically, the main stages of data processing are as follows: Data is collected from various sources for subsequent processing. Reading data: The data undergoes various transformations and manipulations to attain the required structure and format. Transformation: The transformed data is then stored in the target storage. Writing: Schematically, it is represented as shown in the diagram: Challenges After the main stages are implemented, several other stages can be added to enhance and refine the process: This stage involves verifying data compliance with requirements and expectations, including data cleaning to ensure accuracy and completeness. Data quality check: The registration process tracks and documents available data sets. It may also involve data and metadata management for effective data organization. Data registration in the data catalog: This allows visualization of relationships between data assets and ETL processes to understand dependencies. It can help identify potential data quality issues and improve overall data quality. Building data lineage: Additional processing may define the level of data confidentiality and criticality, ensuring proper data protection and security. Data classification: Each of the stages mentioned can be implemented using various technologies and tools, allowing flexibility in choosing the right solution for each specific stage of data processing. Solution Reading data The first step in the ETL process involves reading data from the source. Various types of sources may be used at this stage, such as files, databases, message brokers, and more. The data may also come in different formats, such as CSV, JSON, Parquet, Delta, Hudi, etc. Here is some boilerplate code for reading data from these different sources (implemented as Strategy pattern): from abc import ABC, abstractmethod
from pyspark.sql import SparkSession, DataFrame

# Define the strategy interface
class ReadStrategy(ABC):
    @abstractmethod
    def read_data(self) -> DataFrame:
        pass

# File System Read Strategy - represents a file system reading    
class FileSystemReadStrategy(ReadStrategy):
    def __init__(self, spark: SparkSession, path: str):
        self.spark = spark
        self.path = path
    
    @abstractmethod
    def read_data(self):
        pass
    
# Implement the strategy for CSV
class CSVReadStrategy(FileSystemReadStrategy):
    def read_data(self) -> DataFrame:
        return self.spark.read.format("csv").option("header", "true").load(self.path)


# Implement the strategy for Delta
class DeltaReadStrategy(ReadStrategy):
    def read_data(self) -> DataFrame:
        return self.spark.read.format("delta").load(self.path)

# Implement the strategy for Parquet
class ParquetReadStrategy(ReadStrategy):
    def read_data(self) -> DataFrame:
        return self.spark.read.format("parquet").load(self.path)

# Context class
class DataReader:
    def __init__(self, read_strategy: ReadStrategy):
        self.read_strategy = read_strategy

    def read(self) -> DataFrame:
        return self.read_strategy.read_data() Usage example: spark = SparkSession.builder.appName("our-ETL-job").getOrCreate()
path = "path-to-our-data"
src_df = DataReader(DeltaReadStrategy(spark, path)).read() This approach offers several benefits: It enables the alteration of the reading strategy (source format). It facilitates the addition of new reading strategies without modifying existing code. Each strategy can have its unique parameters. For example, CSV may have a delimiter, while Delta may have a version, etc. Instead of providing a path, we can create a dedicated class, like , which can hold all necessary data reading parameters. ReadConfig It allows the incorporation of additional logic into strategies without affecting the main code. For instance: We can call by default for a file source. input_file_name() If we have a data catalog, we can check whether the table exists there and register it if not. Each strategy can be tested individually. Transformation After reading the data, it must be transformed. This can include various operations like filtering, aggregating, and joining. Since the transformation process can be complex and time-consuming, it's crucial to standardize it for ease of understanding. From my experience, using the method of the DataFrame API is an effective way to standardize this process. This method lets us chain multiple transformations, enhancing code readability. Each transformation can be declared as a separate pure function, performing a specific transformation, and can be tested independently. transform import pyspark.sql.functions as F
from pyspark.sql import DataFrame

desired_columns = ["name", "age", "country"]

def with_country_code(df: DataFrame) -> DataFrame:
    return df.withColumn("country_code", F.when(F.col("country") == "USA", "US").otherwise("Other"))

def with_age_category(df: DataFrame) -> DataFrame:
    return df.withColumn("age_category", F.when(F.col("age") < 18, "child").when((F.col("age") >= 18) & (F.col("age") < 65), "adult").otherwise("senior"))

def filter_adult(df: DataFrame) -> DataFrame:
    return df.filter(F.col("age_category") == "adult")

def select_columns(columns: list) -> callable[[DataFrame], DataFrame]:
    def inner(df: DataFrame) -> DataFrame:
        return df.select(columns)
    return inner

transformed_df = (src_df
                  .transform(with_country_code)
                  .transform(with_age_category)
                  .transform(filter_adult)
                  .transform(select_columns(desired_columns))
                  ) Writing The last stage in the ETL process is writing the transformed data into the target storage, which could be a file system, a database, or a message broker. The loading phase may also incorporate extra steps like data validation, data registration, and data lineage. For this stage, we can apply the same methods as reading data, but with writing strategies. from abc import ABC, abstractmethod
from pyspark.sql import DataFrame, SparkSession

class WriteStrategy(ABC):
    @abstractmethod
    def write_data(self, df: DataFrame):
        pass

class FileSystemWriteStrategy(WriteStrategy):
    def __init__(self, spark: SparkSession, path: str, format: str, mode: str = "overwrite"):
        self.spark = spark
        self.path = path
        self.format = format
        self.mode = mode

    def write_data(self, df: DataFrame):
        df.write.format(self.format).mode(self.mode).save(self.path)

class DataWriter:
    def __init__(self, write_strategy: WriteStrategy):
        self.write_strategy = write_strategy

    def write(self, df: DataFrame):
        self.write_strategy.write_data(df) Usage example: path = "path-to-our-data"
writer = DataWriter(FileSystemWriteStrategy(spark, path, "delta"))
writer.write(transformed_df) Conclusion This implementation standardizes the ETL process, making it more comprehensible and maintainable. It facilitates the addition of new reading and writing strategies without modifying existing code. Each strategy can possess unique parameters, and additional logic can be incorporated into strategies without disrupting the main code. The approach also enables separate testing of each strategy. Moreover, we can introduce additional stages to the ETL process, such as data quality checks, data registration in the data catalog, building data lineage, and data classification.

Dev Standards for Spark-jobs

স্পার্ক-জবসের জন্য ডেভ স্ট্যান্ডার্ড

Entwicklungsstandards für Spark-Jobs

Estándares de desarrollo para trabajos Spark

Normes de développement pour les tâches Spark

स्पार्क-नौकरियों के लिए विकास मानक

Spark ジョブの開発標準

Spark 작업을 위한 개발 표준

Padrões de desenvolvimento para trabalhos Spark

Стандарты разработки для Spark-заданий

Spark işleri için Geliştirme Standartları

Tiêu chuẩn dành cho nhà phát triển cho công việc Spark

Spark 作业的开发标准

Why Monitoring a Distributed Database is More Complex Than You Might Expect

Imagine you're tasked with implementing a geo-fencing feature for your service. At first glance, dropping in an IP GeoLocation API might seem straightforward, but often, project constraints or requirements complicate the simplest solutions. Let's explore our options. Local GeoIP Database Implementation This method involves embedding a geolocation database directly within your application. It offers fast response times as it resides in-memory and operates independently of external services. However, this approach can be resource-intensive and harder to manage. Let’s see it in more detail. Pros Speed: IP lookups in milliseconds or less.
Reliability: No reliance on third-party services reduces failure chances.
Scalability: No extra costs as usage grows. Cons Resource Intensive: Each app instance consumes significant memory.
More Dependencies: Requires packages for decoding and looking up GeoIP data.
Challenging Updates: Updates require manual intervention or another self-hosted service. Using External GeoIP APIs (Often Paid) Opting for a third-party API to handle geolocation queries reduces your application's resource load and ensures access to the most current data, but it comes with its own challenges: Pros Minimal Resource Use: Outsourcing GeoIP data and logic doesn't burden your app's resources.
Ease of Implementation: Usually involves consuming a standard HTTP/GraphQL or gRPC API endpoint.
Fewer Dependencies: Often only requires an HTTP client.
Updates without hassle: Third-party will handle updates Cons Slower Responses: Calls to third-party services can introduce significant latency.
Increased Unreliability: Greater likelihood of failures due to network issues or third-party downtimes.
Scaling Costs: Costs can escalate as usage increases. Introducing Atlas: Combining the Best of Both Worlds Faced with these challenges, I developed Atlas—a robust solution designed to synergize the benefits of both approaches. Atlas acts as a self-hosting HTTP service that automatically manages and serves the latest MaxMind geolocation databases, providing: Low Latency: Similar to local databases, Atlas offers rapid response times. (often in a few milliseconds) Reliability and Ease of Updates: It autonomously handles database updates, removing the typical maintenance burdens. Cost Efficiency: No additional costs with scale in terms of third-party fees. Dead-Simple API: A very simple API that lets you implement quicker and focus on what matters. Open and Free: Atlas is Open Source and freely available under Apache-2.0 License. By hosting Atlas closer to your application but operating like a remote API, it offers a middle ground that maximizes efficiency without the drawbacks of either method we have discussed. Why Rust? Safe, Performant, and Resource-Efficient Atlas is written in Rust, a language chosen for its unparalleled safety, performance, and resource efficiency. Rust's ownership model ensures memory safety without the need for a garbage collector, which is particularly advantageous during database updates—these operations consume minimal memory, avoiding the typical overhead associated with garbage-collected languages. This design choice not only boosts Atlas's performance but also enhances its reliability, making it an ideal solution for managing GeoIP data efficiently and safely. How to Deploy and Use Atlas? Atlas Docker images are automatically built and pushed to Docker Hub via GitHub Actions, making it easy for anyone to use. For detailed instructions on using these Docker images or building Atlas from source and configuration parameters, please refer to the Atlas README.md. Atlas features a straightforward API, documented with OpenAPI 3.0 Specs. You can access the API documentation from the public internet here: https://atlas-rs.fly.dev/swagger-ui/. Here’s an example of how you can use curl to query the demo instance of Atlas (hosted publicly thanks to Fly.io and the GeoLite2 MaxMind database license) to retrieve country information for an IP address 8.8.8.8: > curl https://atlas-rs.fly.dev/geoip/lookup/country/8.8.8.8 | jq
{
  "results": {
    "8.8.8.8": {
      "country": {
        "iso_code": "US",
        // additional fields...
      },
      // additional results...
    }
  },
  "database_build_epoch": 1715091859
} Please note that this demo instance is intended only for demonstration and documentation purposes. Join the Atlas Community and Contribute As a newly introduced open-source project, Atlas is in the early stages of building its community. I am excited about the potential for Atlas to evolve and thrive with contributions from developers like you. Whether you're interested in refining functionality, adding new features, or simply improving documentation, your contributions are welcome and greatly valued. How You Can Contribute Try Atlas: The best way to start is by using Atlas in your projects. Experiment with it, identify any issues, and suggest improvements based on your real-world usage.


Report Issues: If you encounter any bugs or issues while using Atlas, please report them on the GitHub issues page. This feedback is crucial for continual improvement.


Suggest Features: Have ideas for new features or enhancements? Open a discussion on the GitHub repository or contribute directly by submitting a pull request.


Improve Documentation: As Atlas evolves, so too does the need for clear and comprehensive documentation. Whether it's enhancing the current content or writing tutorials and guides, your knowledge-sharing is invaluable.


Spread the Word: If you find Atlas useful, consider sharing it within your networks. Blog about your experiences, talk about Atlas at meetups, or share it on social media. Commitment to Open Source I am relatively new to Rust but bring many years of experience from other technology stacks. This project presents a great opportunity for more experienced Rust developers to contribute, ensuring that Atlas adheres to Rust's best practices and conventions since I know that it is not as good as it can be right now. I am committed to maintaining Atlas as a community-driven project and believe in open, collaborative development. I am eager to see how the community will shape Atlas's path forward and encourage you to join me in this journey, helping to build a tool that serves the needs of developers globally. In the end, I would like to give a special thanks to oschwald for creating the rust maxminddb crate which made Atlas possible. Future Directions As we continue to develop and improve Atlas, my vision is to create a robust, user-friendly minimal service. As of today, Atlas only supports MaxMind databases, but it would be beneficial to have an extensible implementation so more and more formats can be supported. Another aspect I would like to pay attention to is adding more endpoint types such as gRPC so that users can choose the best endpoint based on their requirements and needs. Thank you for taking the time to learn about Atlas. I am excited to see where we can take this project with your support and collaboration! Imagine you're tasked with implementing a geo-fencing feature for your service. At first glance, dropping in an IP GeoLocation API might seem straightforward, but often, project constraints or requirements complicate the simplest solutions. Let's explore our options. Local GeoIP Database Implementation Local GeoIP Database Implementation This method involves embedding a geolocation database directly within your application. It offers fast response times as it resides in-memory and operates independently of external services. However, this approach can be resource-intensive and harder to manage. Let’s see it in more detail. Pros Speed: IP lookups in milliseconds or less. Reliability: No reliance on third-party services reduces failure chances. Scalability: No extra costs as usage grows. Speed : IP lookups in milliseconds or less. Speed Reliability : No reliance on third-party services reduces failure chances. Reliability Scalability : No extra costs as usage grows. Scalability Cons Resource Intensive: Each app instance consumes significant memory. More Dependencies: Requires packages for decoding and looking up GeoIP data. Challenging Updates: Updates require manual intervention or another self-hosted service. Resource Intensive : Each app instance consumes significant memory. Resource Intensive More Dependencies : Requires packages for decoding and looking up GeoIP data. More Dependencies Challenging Updates : Updates require manual intervention or another self-hosted service. Challenging Updates Using External GeoIP APIs (Often Paid) Using External GeoIP APIs (Often Paid) Opting for a third-party API to handle geolocation queries reduces your application's resource load and ensures access to the most current data, but it comes with its own challenges: Pros Minimal Resource Use: Outsourcing GeoIP data and logic doesn't burden your app's resources. Ease of Implementation: Usually involves consuming a standard HTTP/GraphQL or gRPC API endpoint. Fewer Dependencies: Often only requires an HTTP client. Updates without hassle: Third-party will handle updates Minimal Resource Use : Outsourcing GeoIP data and logic doesn't burden your app's resources. Minimal Resource Use Ease of Implementation : Usually involves consuming a standard HTTP/GraphQL or gRPC API endpoint. Ease of Implementation Fewer Dependencies : Often only requires an HTTP client. Fewer Dependencies Updates without hassle : Third-party will handle updates Updates without hassle Cons Slower Responses: Calls to third-party services can introduce significant latency. Increased Unreliability: Greater likelihood of failures due to network issues or third-party downtimes. Scaling Costs: Costs can escalate as usage increases. Slower Responses : Calls to third-party services can introduce significant latency. Slower Responses Increased Unreliability : Greater likelihood of failures due to network issues or third-party downtimes. Increased Unreliability Scaling Costs : Costs can escalate as usage increases. Scaling Costs Introducing Atlas : Combining the Best of Both Worlds Introducing Atlas Faced with these challenges, I developed Atlas —a robust solution designed to synergize the benefits of both approaches. Atlas acts as a self-hosting HTTP service that automatically manages and serves the latest MaxMind geolocation databases, providing: Atlas Low Latency: Similar to local databases, Atlas offers rapid response times. (often in a few milliseconds) Low Latency : Similar to local databases, Atlas offers rapid response times. (often in a few milliseconds) Low Latency Reliability and Ease of Updates: It autonomously handles database updates, removing the typical maintenance burdens. Reliability and Ease of Updates : It autonomously handles database updates, removing the typical maintenance burdens. Reliability and Ease of Updates Cost Efficiency: No additional costs with scale in terms of third-party fees. Cost Efficiency : No additional costs with scale in terms of third-party fees. Cost Efficiency Dead-Simple API: A very simple API that lets you implement quicker and focus on what matters. Dead-Simple API : A very simple API that lets you implement quicker and focus on what matters. Dead-Simple API Open and Free: Atlas is Open Source and freely available under Apache-2.0 License. Open and Free: Atlas is Open Source and freely available under Apache-2.0 License. Open and Free : Atlas is Open Source and freely available under Apache-2.0 License. Open and Free By hosting Atlas closer to your application but operating like a remote API, it offers a middle ground that maximizes efficiency without the drawbacks of either method we have discussed. Why Rust? Safe, Performant, and Resource-Efficient Why Rust? Atlas is written in Rust, a language chosen for its unparalleled safety, performance, and resource efficiency. Rust's ownership model ensures memory safety without the need for a garbage collector, which is particularly advantageous during database updates—these operations consume minimal memory, avoiding the typical overhead associated with garbage-collected languages. This design choice not only boosts Atlas's performance but also enhances its reliability, making it an ideal solution for managing GeoIP data efficiently and safely. How to Deploy and Use Atlas? Atlas Docker images are automatically built and pushed to Docker Hub via GitHub Actions, making it easy for anyone to use. For detailed instructions on using these Docker images or building Atlas from source and configuration parameters, please refer to the Atlas README.md . Atlas README.md Atlas features a straightforward API, documented with OpenAPI 3.0 Specs. You can access the API documentation from the public internet here: https://atlas-rs.fly.dev/swagger-ui/ . https://atlas-rs.fly.dev/swagger-ui/ Here’s an example of how you can use curl to query the demo instance of Atlas (hosted publicly thanks to Fly.io and the GeoLite2 MaxMind database license) to retrieve country information for an IP address 8.8.8.8 : Fly.io 8.8.8.8 > curl https://atlas-rs.fly.dev/geoip/lookup/country/8.8.8.8 | jq
{
  "results": {
    "8.8.8.8": {
      "country": {
        "iso_code": "US",
        // additional fields...
      },
      // additional results...
    }
  },
  "database_build_epoch": 1715091859
} > curl https://atlas-rs.fly.dev/geoip/lookup/country/8.8.8.8 | jq
{
  "results": {
    "8.8.8.8": {
      "country": {
        "iso_code": "US",
        // additional fields...
      },
      // additional results...
    }
  },
  "database_build_epoch": 1715091859
} Please note that this demo instance is intended only for demonstration and documentation purposes. Please note that this demo instance is intended only for demonstration and documentation purposes. Join the Atlas Community and Contribute Join the Atlas Community and Contribute As a newly introduced open-source project, Atlas is in the early stages of building its community. I am excited about the potential for Atlas to evolve and thrive with contributions from developers like you. Whether you're interested in refining functionality, adding new features, or simply improving documentation, your contributions are welcome and greatly valued. How You Can Contribute Try Atlas: The best way to start is by using Atlas in your projects. Experiment with it, identify any issues, and suggest improvements based on your real-world usage. Report Issues: If you encounter any bugs or issues while using Atlas, please report them on the GitHub issues page. This feedback is crucial for continual improvement. Suggest Features: Have ideas for new features or enhancements? Open a discussion on the GitHub repository or contribute directly by submitting a pull request. Improve Documentation: As Atlas evolves, so too does the need for clear and comprehensive documentation. Whether it's enhancing the current content or writing tutorials and guides, your knowledge-sharing is invaluable. Spread the Word: If you find Atlas useful, consider sharing it within your networks. Blog about your experiences, talk about Atlas at meetups, or share it on social media. Try Atlas: The best way to start is by using Atlas in your projects. Experiment with it, identify any issues, and suggest improvements based on your real-world usage. Try Atlas : The best way to start is by using Atlas in your projects. Experiment with it, identify any issues, and suggest improvements based on your real-world usage. Try Atlas Report Issues: If you encounter any bugs or issues while using Atlas, please report them on the GitHub issues page. This feedback is crucial for continual improvement. Report Issues : If you encounter any bugs or issues while using Atlas, please report them on the GitHub issues page. This feedback is crucial for continual improvement. Report Issues Suggest Features: Have ideas for new features or enhancements? Open a discussion on the GitHub repository or contribute directly by submitting a pull request. Suggest Features : Have ideas for new features or enhancements? Open a discussion on the GitHub repository or contribute directly by submitting a pull request. Suggest Features Improve Documentation: As Atlas evolves, so too does the need for clear and comprehensive documentation. Whether it's enhancing the current content or writing tutorials and guides, your knowledge-sharing is invaluable. Improve Documentation : As Atlas evolves, so too does the need for clear and comprehensive documentation. Whether it's enhancing the current content or writing tutorials and guides, your knowledge-sharing is invaluable. Improve Documentation Spread the Word: If you find Atlas useful, consider sharing it within your networks. Blog about your experiences, talk about Atlas at meetups, or share it on social media. Spread the Word : If you find Atlas useful, consider sharing it within your networks. Blog about your experiences, talk about Atlas at meetups, or share it on social media. Spread the Word Commitment to Open Source I am relatively new to Rust but bring many years of experience from other technology stacks. This project presents a great opportunity for more experienced Rust developers to contribute, ensuring that Atlas adheres to Rust's best practices and conventions since I know that it is not as good as it can be right now. I am committed to maintaining Atlas as a community-driven project and believe in open, collaborative development. I am eager to see how the community will shape Atlas's path forward and encourage you to join me in this journey, helping to build a tool that serves the needs of developers globally. In the end, I would like to give a special thanks to oschwald for creating the rust maxminddb crate which made Atlas possible. special thanks to oschwald oschwald maxminddb Future Directions As we continue to develop and improve Atlas, my vision is to create a robust, user-friendly minimal service. As of today, Atlas only supports MaxMind databases, but it would be beneficial to have an extensible implementation so more and more formats can be supported. Another aspect I would like to pay attention to is adding more endpoint types such as gRPC so that users can choose the best endpoint based on their requirements and needs. Thank you for taking the time to learn about Atlas. I am excited to see where we can take this project with your support and collaboration!

IP Geo-Location: Local Database vs. Remote API—Finding the Middle Ground With Atlas

Get Ready, Future Web Developers! Let's create a simple HTML website that connects to a Postgres Database. For this example, we will be using JavaScript (Node JS, and Express JS) for its middleware functionalities. Whether you want to showcase your portfolio, run a blog, or complete a due mini project on web development, web development is a skill that's still in demand. The Learning Path Ahead We'll dive into the process of building a simple HTML website from scratch. We'll explore how to integrate a Postgres database, using JavaScript as middleware to handle API requests. To spice things up, we can also use Bootstrap5 to design the webpage! But that will be covered in a different blog. Technologies Used: HTML5
Postgres (Database)
Express JS (Backend) Prerequisites Make sure Postgres is downloaded and ready to use. Also, download Node JS for installing npm packages like Express JS, Body Parser and Postgres APIs.
Download Postgres here
Download NodeJS here Step 1: Setup Postgres Database Create a new Postgres Database either using psql, which is the CLI for running SQL commands or a graphical user interface like pgAdmin (which comes pre-installed). For this demonstration, we will be using psql for creating the database. Open psql and enter the credentials to get started. Create a database called webapp and connect to it!
(You can use any name you want for your project!) CREATE database webapp; \c webapp Start building your table in this database by CREATE TABLE command. You can use Postgres docs to guide you with the commands.
For this demonstration, we are creating a student database that stores Name, Year and Branch. (Add more attributes if you want) CREATE TABLE student 
(name varchar(20), semester int, branch varchar(20)); \d student Now, we can start uploading some dummy data to the table via INSERT command INSERT INTO student VALUES
('Abel', 4, 'CE'), 
('John', 6, 'ME'), 
('Doe', 2, 'ETC'); Once we are done, adding the dummy data, we can view the Student table with the below command SELECT * FROM student; If you followed the steps till now, you would get the below student table! Step 2: Create a simple HTML Website Below is the Boilerplate HTML code for a simple website: <!DOCTYPE html>
<html lang='en'>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width">
  <title> Student Management System </title>
</head>
<body>
  <h1> Student Management System </h1>
</body>
</html> Let's start adding the custom div blocks as per our needs: ...
<body>
  <h1> Student Management System </h1>
  <p> Here, are the student details as below: </p>
  <div id='students_list'></div>
... students_list div block is for showing the list of students that have been registered to the database. We will be using Express JS to fetch the data from Postgres and showcase it in the block. Step 3: Connecting HTML to Postgres via Express JS Install the necessary libraries first in your Command Prompt or Terminal npm install express body-parser pg Let's start building the script.js file for connecting HTML and PostgreSQL. Firstly, we require importing the necessary libraries for handling requests and establishing a connection // Import required modules
const express = require('express');
const { Pool } = require('pg');
const path = require('path');
const bodyParser = require('body-parser'); express works as a backend service, for parsing requests from the HTML webapp
pg is a Postgres API for establishing the connection
path provides utilities for managing directory paths
body-parser is a middle-ware for extracting data from POST requests
(We will be understanding them in depth as we move along) Let's now create an express application connection and also, define the port at which the server will be listening to. // Connect and Create an Express Application
const app = express();
const port = 3000; // By default, its 3000, you can customize Establish a connection to Postgres by creating an instance of the Pool object. Add the necessary values to match your Postgres setup. // Create a Postgres Connection
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'webapp',
  password: 'password', // Change to your password
  port: 5432, // Default Port
}); Add functionalities to register static files via express.static() middleware. It specifies the root directory from which to serve static files app.use(express.static(path.join(''))); Now, we have to parse HTML requests sent from the app. In simple words, it's a middleware used to recieve data from users, such as forms or uploads app.use(bodyParser.urlencoded({ extended: false })); (Don't add the above line if you are not planning to take input from users. We will be adding a registeration form in the next blog and that's why we require body-parser) Set up a Route handler for the root URL ('/'). So that, when a GET request is made to the root URL, the server responds by sending the "index.html" file located in the same directory // Setup Route handler
app.get('/', (req, res) => {
  res.sendFile(path.join(__dirname, '', 'index.html'));
}); Now, comes the main part! We will now be setting up a route handler for '/students' URL with the HTTP GET method. This handler retrieves student data (from query) from the Postgres Database. // Route handler for GET student data
app.get('/students', (req, res) => {
  const query = 'SELECT * FROM student;';

  pool.query(query, (error, result) => {
    if (error) {
      console.error('Error occurred:', error);
      res.status(500).send('An error occurred while retrieving data from the database.');
    } else {
      const students = result.rows;
      res.json(students);
    }
  });
}); (Make sure the brackets are closed properly) Now, let's specify the line that listens to the server and when the time is due, it responds with its requests. As the below command listens, it logs a message to console. // Listening to Requests
app.listen(port, () => {
  console.log(`Server listening on port ${port}`);
}); (Good for debugging purposes) Done! script.js is finally complete. Let's make the changes in the index.html file for showcasing the necessary details. ...
  <div id='students_list'></div>
  <script>
  // Fetch data via requests
  fetch('/students')
    .then(response => response.json())
    .then(data => {
    const studentList = data.map(
      student => `<li> About ${student.name} : ${student.branch} - ${student.semester}th Sem</li>`
    ).join('');
    document.getElementById('students_list').innerHTML = `<ul>${studentList}</ul>`;
  })
    .catch(error => console.error('Error occurred:', error));
  </script>
... Run the Website Open Terminal and go to the directory where index.html and script.js is stored and run the following command: node script.js If everything works all right, it should display the content "Server listening on port 3000" Now, you need to go to http://localhost:3000/ where you can see a simple HTML website showcasing the data you had entered! Voila! That's it! Now, use your creativity and knowledge to explore these web development concepts and create more interesting websites! I will also be adding another blog on how to create a registration system and how to design it, which will be coming soon. Follow for more and stay tuned! Until then, Keep Coding! Get Ready, Future Web Developers! Let's create a simple HTML website that connects to a Postgres Database. For this example, we will be using JavaScript (Node JS, and Express JS) for its middleware functionalities. Whether you want to showcase your portfolio, run a blog, or complete a due mini project on web development, web development is a skill that's still in demand. The Learning Path Ahead We'll dive into the process of building a simple HTML website from scratch. We'll explore how to integrate a Postgres database, using JavaScript as middleware to handle API requests. To spice things up, we can also use Bootstrap5 to design the webpage! But that will be covered in a different blog. Technologies Used: HTML5 Postgres (Database) Express JS (Backend) HTML5 Postgres (Database) Express JS (Backend) Prerequisites Make sure Postgres is downloaded and ready to use. Also, download Node JS for installing npm packages like Express JS, Body Parser and Postgres APIs.
Download Postgres here Download NodeJS here here here Step 1: Setup Postgres Database Create a new Postgres Database either using psql , which is the CLI for running SQL commands or a graphical user interface like pgAdmin (which comes pre-installed). For this demonstration, we will be using psql for creating the database. psql psql Open psql and enter the credentials to get started. psql Create a database called webapp and connect to it! (You can use any name you want for your project!) webapp (You can use any name you want for your project!) CREATE database webapp; CREATE database webapp; \c webapp \c webapp Start building your table in this database by CREATE TABLE command. You can use Postgres docs to guide you with the commands.
For this demonstration, we are creating a student database that stores Name , Year and Branch . (Add more attributes if you want) CREATE TABLE student Name Year Branch (Add more attributes if you want) CREATE TABLE student 
(name varchar(20), semester int, branch varchar(20)); CREATE TABLE student 
(name varchar(20), semester int, branch varchar(20)); \d student \d student Now, we can start uploading some dummy data to the table via INSERT command INSERT INTO student VALUES
('Abel', 4, 'CE'), 
('John', 6, 'ME'), 
('Doe', 2, 'ETC'); INSERT INTO student VALUES
('Abel', 4, 'CE'), 
('John', 6, 'ME'), 
('Doe', 2, 'ETC'); Once we are done, adding the dummy data, we can view the Student table with the below command Student SELECT * FROM student; SELECT * FROM student; If you followed the steps till now, you would get the below student table! student Step 2: Create a simple HTML Website Below is the Boilerplate HTML code for a simple website: <!DOCTYPE html>
<html lang='en'>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width">
  <title> Student Management System </title>
</head>
<body>
  <h1> Student Management System </h1>
</body>
</html> <!DOCTYPE html>
<html lang='en'>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width">
  <title> Student Management System </title>
</head>
<body>
  <h1> Student Management System </h1>
</body>
</html> Let's start adding the custom div blocks as per our needs: ...
<body>
  <h1> Student Management System </h1>
  <p> Here, are the student details as below: </p>
  <div id='students_list'></div>
... ...
<body>
  <h1> Student Management System </h1>
  <p> Here, are the student details as below: </p>
  <div id='students_list'></div>
... students_list div block is for showing the list of students that have been registered to the database. We will be using Express JS to fetch the data from Postgres and showcase it in the block. students_list Step 3: Connecting HTML to Postgres via Express JS Install the necessary libraries first in your Command Prompt or Terminal Command Prompt npm install express body-parser pg npm install express body-parser pg Let's start building the script.js file for connecting HTML and PostgreSQL. Firstly, we require importing the necessary libraries for handling requests and establishing a connection script.js // Import required modules
const express = require('express');
const { Pool } = require('pg');
const path = require('path');
const bodyParser = require('body-parser'); // Import required modules
const express = require('express');
const { Pool } = require('pg');
const path = require('path');
const bodyParser = require('body-parser'); express works as a backend service, for parsing requests from the HTML webapp pg is a Postgres API for establishing the connection path provides utilities for managing directory paths body-parser is a middle-ware for extracting data from POST requests (We will be understanding them in depth as we move along) express pg path body-parser (We will be understanding them in depth as we move along) Let's now create an express application connection and also, define the port at which the server will be listening to. // Connect and Create an Express Application
const app = express();
const port = 3000; // By default, its 3000, you can customize // Connect and Create an Express Application
const app = express();
const port = 3000; // By default, its 3000, you can customize Establish a connection to Postgres by creating an instance of the Pool object. Add the necessary values to match your Postgres setup. Pool // Create a Postgres Connection
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'webapp',
  password: 'password', // Change to your password
  port: 5432, // Default Port
}); // Create a Postgres Connection
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'webapp',
  password: 'password', // Change to your password
  port: 5432, // Default Port
}); Add functionalities to register static files via express.static() middleware. It specifies the root directory from which to serve static files express.static() app.use(express.static(path.join(''))); app.use(express.static(path.join(''))); Now, we have to parse HTML requests sent from the app. In simple words, it's a middleware used to recieve data from users, such as forms or uploads app.use(bodyParser.urlencoded({ extended: false })); app.use(bodyParser.urlencoded({ extended: false })); (Don't add the above line if you are not planning to take input from users. We will be adding a registeration form in the next blog and that's why we require body-parser) (Don't add the above line if you are not planning to take input from users. We will be adding a registeration form in the next blog and that's why we require body-parser) Set up a Route handler for the root URL ('/'). So that, when a GET request is made to the root URL, the server responds by sending the "index.html" file located in the same directory // Setup Route handler
app.get('/', (req, res) => {
  res.sendFile(path.join(__dirname, '', 'index.html'));
}); // Setup Route handler
app.get('/', (req, res) => {
  res.sendFile(path.join(__dirname, '', 'index.html'));
}); Now, comes the main part! We will now be setting up a route handler for '/students' URL with the HTTP GET method. This handler retrieves student data (from query) from the Postgres Database. // Route handler for GET student data
app.get('/students', (req, res) => {
  const query = 'SELECT * FROM student;';

  pool.query(query, (error, result) => {
    if (error) {
      console.error('Error occurred:', error);
      res.status(500).send('An error occurred while retrieving data from the database.');
    } else {
      const students = result.rows;
      res.json(students);
    }
  });
}); // Route handler for GET student data
app.get('/students', (req, res) => {
  const query = 'SELECT * FROM student;';

  pool.query(query, (error, result) => {
    if (error) {
      console.error('Error occurred:', error);
      res.status(500).send('An error occurred while retrieving data from the database.');
    } else {
      const students = result.rows;
      res.json(students);
    }
  });
}); (Make sure the brackets are closed properly) (Make sure the brackets are closed properly) Now, let's specify the line that listens to the server and when the time is due, it responds with its requests. As the below command listens, it logs a message to console. // Listening to Requests
app.listen(port, () => {
  console.log(`Server listening on port ${port}`);
}); // Listening to Requests
app.listen(port, () => {
  console.log(`Server listening on port ${port}`);
}); (Good for debugging purposes) (Good for debugging purposes) Done! script.js is finally complete. Let's make the changes in the index.html file for showcasing the necessary details. script.js index.html ...
  <div id='students_list'></div>
  <script>
  // Fetch data via requests
  fetch('/students')
    .then(response => response.json())
    .then(data => {
    const studentList = data.map(
      student => `<li> About ${student.name} : ${student.branch} - ${student.semester}th Sem</li>`
    ).join('');
    document.getElementById('students_list').innerHTML = `<ul>${studentList}</ul>`;
  })
    .catch(error => console.error('Error occurred:', error));
  </script>
... ...
  <div id='students_list'></div>
  <script>
  // Fetch data via requests
  fetch('/students')
    .then(response => response.json())
    .then(data => {
    const studentList = data.map(
      student => `<li> About ${student.name} : ${student.branch} - ${student.semester}th Sem</li>`
    ).join('');
    document.getElementById('students_list').innerHTML = `<ul>${studentList}</ul>`;
  })
    .catch(error => console.error('Error occurred:', error));
  </script>
... Run the Website Open Terminal and go to the directory where index.html and script.js is stored and run the following command: index.html script.js node script.js node script.js If everything works all right, it should display the content "Server listening on port 3000" Now, you need to go to http://localhost:3000/ where you can see a simple HTML website showcasing the data you had entered! http://localhost:3000/ Voila! That's it! Voila! That's it! Now, use your creativity and knowledge to explore these web development concepts and create more interesting websites! I will also be adding another blog on how to create a registration system and how to design it, which will be coming soon. Follow for more and stay tuned! Until then, Keep Coding!

Build a Simple HTML Website with Postgres Database Connectivity

Postgres ডাটাবেস সংযোগ সহ একটি সাধারণ HTML ওয়েবসাইট তৈরি করুন

Erstellen Sie eine einfache HTML-Website mit Postgres-Datenbankkonnektivität

Cree un sitio web HTML simple con conectividad de base de datos Postgres

Créez un site Web HTML simple avec la connectivité de la base de données Postgres

Postgres डेटाबेस कनेक्टिविटी के साथ एक सरल HTML वेबसाइट बनाएं

Postgres データベース接続を備えたシンプルな HTML ウェブサイトを構築する

Postgres 데이터베이스 연결을 사용하여 간단한 HTML 웹사이트 구축

Crie um site HTML simples com conectividade de banco de dados Postgres

Создайте простой HTML-сайт с возможностью подключения к базе данных Postgres

Postgres Veritabanı Bağlantısı ile Basit Bir HTML Web Sitesi Oluşturun

Xây dựng một trang web HTML đơn giản với kết nối cơ sở dữ liệu Postgres

使用 Postgres 数据库连接构建一个简单的 HTML 网站

Auto-Increment Columns in Databases: A Simple Trick That Makes a Big Difference

Top 10 Websites to Learn SQL and Database Online

Step 1: Install the necessary libraries To connect to a database and query data, you need to install the following two libraries: pandas: A Python library for data analysis, including functions for reading and writing data.
sqlalchemy: A Python library for interacting with relational databases, allowing you to use Python with various databases. You can install these libraries using the following command in the command prompt or terminal: pip install pandas
pip install sqlalchemy Step 2: Connect to the database Connecting to a database requires the following information: Database Type: The type of database you want to connect to, such as MySQL, PostgreSQL, etc.
Host Name: The host name or IP address where the database is located.
Port Number: The port number of the database, usually the default port number.
Username: The username required to connect to the database.
Password: The password required to connect to the database.
Database Name: The name of the database you want to connect to. You can use the following Python code to connect to the database: from sqlalchemy import create_engine

# Connect to a MySQL database
engine = create_engine('mysql://username:password@hostname:port/databasename')

# Connect to a PostgreSQL database
engine = create_engine('postgresql://username:password@hostname:port/databasename')

# Connect to an Oracle database, requires cx_Oracle library to be installed
engine = create_engine('oracle+cx_oracle://username:password@hostname:port/databasename') Step 3: Query data using the read_sql function Use the read_sql function from pandas to query data from the database. The read_sql function requires two parameters: SQL Query: The SQL query you want to execute.
Database Connection: The database connection you created earlier. Here is an example of querying data: import pandas as pd

# Execute SQL query and store the result in a DataFrame
df = pd.read_sql('SELECT * FROM mytable', engine)

# Print the DataFrame
print(df) Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn or X(@Luca_DataTeam). Happy exploring!👋 Step 1: Install the necessary libraries To connect to a database and query data, you need to install the following two libraries: pandas: A Python library for data analysis, including functions for reading and writing data. sqlalchemy: A Python library for interacting with relational databases, allowing you to use Python with various databases. pandas : A Python library for data analysis, including functions for reading and writing data. pandas sqlalchemy : A Python library for interacting with relational databases, allowing you to use Python with various databases. sqlalchemy You can install these libraries using the following command in the command prompt or terminal: pip install pandas
pip install sqlalchemy pip install pandas
pip install sqlalchemy Step 2: Connect to the database Connecting to a database requires the following information: Database Type: The type of database you want to connect to, such as MySQL, PostgreSQL, etc. Host Name: The host name or IP address where the database is located. Port Number: The port number of the database, usually the default port number. Username: The username required to connect to the database. Password: The password required to connect to the database. Database Name: The name of the database you want to connect to. Database Type : The type of database you want to connect to, such as MySQL, PostgreSQL, etc. Database Type Host Name : The host name or IP address where the database is located. Host Name Port Number : The port number of the database, usually the default port number. Port Number Username : The username required to connect to the database. Username Password : The password required to connect to the database. Password Database Name : The name of the database you want to connect to. Database Name You can use the following Python code to connect to the database: from sqlalchemy import create_engine

# Connect to a MySQL database
engine = create_engine('mysql://username:password@hostname:port/databasename')

# Connect to a PostgreSQL database
engine = create_engine('postgresql://username:password@hostname:port/databasename')

# Connect to an Oracle database, requires cx_Oracle library to be installed
engine = create_engine('oracle+cx_oracle://username:password@hostname:port/databasename') from sqlalchemy import create_engine

# Connect to a MySQL database
engine = create_engine('mysql://username:password@hostname:port/databasename')

# Connect to a PostgreSQL database
engine = create_engine('postgresql://username:password@hostname:port/databasename')

# Connect to an Oracle database, requires cx_Oracle library to be installed
engine = create_engine('oracle+cx_oracle://username:password@hostname:port/databasename') Step 3: Query data using the read_sql function Use the read_sql function from pandas to query data from the database. The read_sql function requires two parameters: SQL Query: The SQL query you want to execute. Database Connection: The database connection you created earlier. SQL Query : The SQL query you want to execute. SQL Query Database Connection : The database connection you created earlier. Database Connection Here is an example of querying data: import pandas as pd

# Execute SQL query and store the result in a DataFrame
df = pd.read_sql('SELECT * FROM mytable', engine)

# Print the DataFrame
print(df) import pandas as pd

# Execute SQL query and store the result in a DataFrame
df = pd.read_sql('SELECT * FROM mytable', engine)

# Print the DataFrame
print(df) Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn or X (@Luca_DataTeam). Happy exploring!👋 LinkedIn X

How to Connect to Oracle, MySql and PostgreSQL Databases Using Python

Storing Passwords In A Database: A Better Way to Do So

In this article, I’ll guide you through C# Testcontainers for MongoDB! I love using Testcontainers for local database work when I don’t want to think about managing the overhead of spinning up and tearing down a database, or managing something in the cloud. MongoDB is no exception here as the C# Testcontainers NuGet package that we have for MongoDB is a dream to work with. In this article, I’ll introduce you to the basics of Testcontainers. From there, I’ll demonstrate how simple it is to interact with your newly created database via the C# MongoDB.Driver NuGet package — almost like you wouldn’t even know there was a container spun up! Let’s dive into it. Overview of C# Testcontainers Testcontainers is an awesome NuGet package that can greatly enhance your testing and local development workflow when building .NET applications. It provides a simple and effective way to manage Docker containers, allowing you to spin up lightweight and isolated instances and have them tear down like nothing ever happened! That means with Testcontainers, you no longer have to worry about setting up and tearing down test databases or dealing with complex configuration files. It handles all the details of container management, making this process feel trivial — I sure wouldn’t want to handle this manually. Some of the key benefits of Testcontainers include: Its ability to provide a consistent and reproducible test environment. You can define the exact service version and configuration that you require, ensuring that your test results are reliable and consistent across different environments. The speed and flexibility it brings to your testing and development workflow. By using lightweight Docker containers, you can quickly spin up and tear down test environments on demand. No configuring cloud or shared resources ahead of time… Just on-the-fly awesomeness. Seamless integration with existing testing frameworks and tools in the DotNet ecosystem, such as Xunit, NUnit, and BenchmarkDotNet. This allows you to easily incorporate containerized service instances into your existing test suites without any major modifications. For this article, we’ll be focusing on how we can use C# Testcontainers for MongoDB in particular. Much of the advice covered here will be applicable to other services, especially databases, but we’ll keep our sights set on MongoDB. Setting Up Testcontainers in C# To get started with Testcontainers in C#, you’ll need to install and configure the necessary dependencies in your project. Follow the steps below to set up Testcontainers for MongoDB in C#. 1. Installing Testcontainers Generally, the first step would be to install the Testcontainers NuGet package in your C# project. You would open the NuGet Package Manager Console, and run the following command: Install-Package Testcontainers However, we need the MongoDB flavor of this package (I am using version 3.8.0 at the time of writing) which includes the base Testcontainers package. You can install it via the package manager user interface or with this command: Install-Package Testcontainers.MonogoDB 2. Configuring Testcontainers for MongoDB Once you have installed Testcontainers, you need to configure it for MongoDB. This involves setting up a MongoDB container with the required settings. Here is an example of how you can configure Testcontainers for MongoDB in C#: using Testcontainers.MongoDb;

MongoDbContainer container = new MongoDbBuilder()
    .WithImage("mongo:latest")
    .Build(); In the above example, we create a new instance of the , and specify the MongoDB image and port binding. The method sets the image for the MongoDB container, so using “mongo:latest” will always pull the latest image. MongoDbBuilder WithImage Optionally, we could use port binding to configure which ports we want to be using explicitly: using Testcontainers.MongoDb;

MongoDbContainer container = new MongoDbBuilder()
    .WithImage("mongo:latest")
    .WithPortBinding(1337, 27017)
    .Build(); The code above uses method binds the container’s port 27017 to the host machine’s port 1337. We’ll see in the upcoming sections that unless you have a specific need to bind ports, you can potentially just leave this be. We can get a dynamic connection string, which is very convenient! WithPortBinding 3. Starting and Stopping the MongoDB Container After configuring Testcontainers for MongoDB, you can start and stop the container as needed. Here are code examples for starting and stopping the container: To start the MongoDB container: await container.StartAsync(); To stop the MongoDB container: await _container.StopAsync(); Remember the MongoDbContainer type can be disposed of, so call to clean up when you’re ready. DisposeAsync Using C# Testcontainers for MongoDB in Practice Now that we’ve seen how to spin up and tear down containers, what are we supposed to do with them? We have plenty of options, and truly, the limit is your own imagination (and time, money, and other resources…)! Most people leverage C# Testcontainers for writing integration or . You can match up Testcontainers with xUnit or NUnit, for example, and write tests that interact with a real database. functional tests For me, I needed to go wire C# Testcontainers for MongoDB up to BenchmarkDotNet so I could ! A similar use case where I needed transient local databases, but not necessarily for a test framework. benchmark inserting records into MongoDB Regardless, we need to be able to connect to these MongoDB database containers from C#, and these next subsections will cover that. Connecting to C# Testcontainers for MongoDB with MongoDB.Driver Let’s assume you read the earlier sections and you have the correct MongoDB package installed for Testcontainers. If you skimmed ahead to this section, go back, read it, and get the right package. Otherwise, you’ll be scratching your head about why you can’t find the right dependencies! You’ll also need to get the MongoDB.Driver NuGet package installed. This is what we’re going to be using to establish a connection to the database container that we spun up. I have other articles you can read if you want more explanation on how MongoDB.Driver works in general: MongoDB in C#: Simplified Guide For Inserting Data MongoDB Filtering in C# – Beginner’s Guide For Easy Filters How To Delete Documents From MongoDB In C# – What You Need To Know How To Update MongoDB Documents in C# – What You Need To Know With the right packages ready to go, we can combine the code we saw earlier with some MongoDB driver code to get things glued together: using MongoDB.Bson;
using MongoDB.Driver;

using Testcontainers.MongoDb;

MongoDbContainer container = new MongoDbBuilder()
    .WithImage("mongo:latest")
    .Build();
await container.StartAsync();
string connectionString = container.GetConnectionString();

MongoClient mongoClient = new MongoClient(connectionString);
IMongoDatabase database = mongoClient.GetDatabase("test");
IMongoCollection<BsonDocument> collection = database.GetCollection<BsonDocument>("test"); In the example above, we can call on the newly spun-up MongoDB container from Testcontainers. The awesome part about this is that no matter how you configure your Testcontainer for MongoDB using the builder pattern we see in this code and earlier in the article, will get you what you need to connect. GetConnectionString() GetConnectionString() Because takes in a connection string as a single argument, it’s trivial to get access to the MongoDB database to start working with it right away! MongoClient Performing CRUD Operations on the MongoDB Testcontainer Now that we have our MongoDB Testcontainer set up and we have a connected to it, we can start performing CRUD operations on it. The connection details provided by the container instance made it a breeze to combine these two things, and truly, we can focus on just the MongoDB.Driver method calls for CRUD. MongoClient Here’s an example of performing a simple CRUD operation: // using code from earlier examples...

// Create
await collection.InsertOneAsync(new BsonDocument()
{
    ["Name"] = "Nick Cosentino",
});

// Read
var filterBuilder = Builders<BsonDocument>.Filter;
var filter = filterBuilder.Eq("Name", "Nick Cosentino");
var results = collection.Find(filter);

// Update
var updateBuilder = Builders<BsonDocument>.Update;
var update = updateBuilder.Set("Name", "Dev Leader");
collection.UpdateOne(filter, update);

// Delete
filter = filterBuilder.Eq("Name", "Dev Leader");
collection.DeleteOne(filter); Using the previous code snippets, we were already up and running with our MongoDB container instance and a . As a result, you can see in the code snippet above that we can directly work with our that we obtained from the prior setup. MongoClient IMongoCollection<BsonDocument> This helps illustrate that once you have the MongoDB docker container up and running via Testcontainers, you don’t have to treat things any special way once you connect to it! Wrapping Up C# Testcontainers for MongoDB To conclude, C# Testcontainers for MongoDB is an incredibly simple way to get a transient MongoDB data store up and running for your testing and development. While this article didn’t focus on specific use cases, hopefully, you have a better understanding of how transparent it feels to layer a MongoDB driver on top of Testcontainers to interact with MongoDB. Given that C# Testcontainers takes the headache out of the setup, teardown, and even connection string management, it’s an easy recommendation from me if you want to get up and running with MongoDB for local usage. If you found this useful and you’re looking for more learning opportunities, consider , and check out my ! subscribing to my free weekly software engineering newsletter free videos on YouTube Also published here

Easily Run Local Databases Using C# Testcontainers for MongoDB

MongoDB-এর জন্য C# টেস্ট কনটেইনার ব্যবহার করে সহজেই স্থানীয় ডেটাবেস চালান

Führen Sie ganz einfach lokale Datenbanken mit C#-Testcontainern für MongoDB aus

Ejecute fácilmente bases de datos locales utilizando C# Testcontainers para MongoDB

Exécutez facilement des bases de données locales à l'aide de C# Testcontainers pour MongoDB

MongoDB के लिए C# टेस्टकंटेनर का उपयोग करके आसानी से स्थानीय डेटाबेस चलाएँ

MongoDB 用の C# Testcontainers を使用してローカル データベースを簡単に実行する

MongoDB용 C# 테스트 컨테이너를 사용하여 로컬 데이터베이스를 쉽게 실행

Execute facilmente bancos de dados locais usando C# Testcontainers para MongoDB

Легко запускайте локальные базы данных с помощью тестовых контейнеров C# для MongoDB

MongoDB için C# Testcontainer'larını Kullanarak Yerel Veritabanlarını Kolayca Çalıştırın

Dễ dàng chạy cơ sở dữ liệu cục bộ bằng C# Testcontainer cho MongoDB

使用 MongoDB 的 C# 测试容器轻松运行本地数据库

Need to provide database self-service to internal teams? Can't spare the resources to build and maintain your own private DBaaS solution? Sick of cloud DBaaS providers locking you in? is a cloud-native database platform to deploy and manage enterprise-grade PostgreSQL, MongoDB and MySQL database clusters. Percona Everest With Percona Everest, you can: Enable DBA teams to regain control over data and database configuration Empower developers to deploy code faster and self-service provision highly performant, production-ready database clusters Free your entire organization from vendor lock-in and restrictive subscriptions Benefits of Percona Everest Truly , Percona Everest offers the benefits of automated database provisioning and management without the need to build or maintain an in-house database management platform. open source No data lock-in or restrictive contracts Reduced total cost of ownership (TCO) Complete data sovereignty to meet any compliance requirement Fully customizable database configurations Right-sized database deployments Frictionless developer self-service Backing of highly skilled database management experts Key features of Percona Everest Database management via a single pane of glass Percona Everest enables you to manage complex database environments through a single pane of glass or API. Create, configure, deploy, update, and upgrade with zero downtime Backup, restore, restart, suspend/resume, or delete database clusters Complete customization Customize load balancing settings, settings for network exposure, and resource settings like node count, instance type, CPU, memory, storage, etc. Standardized database deployments Create ready-to-use database clusters of enterprise-grade MySQL, PostgreSQL, and MongoDB, with the ability to customize to support a variety of topologies or deployments. Percona Everest leverages to deploy Cloud-Native Percona Distributions. Percona Operators Built-in observability tools Benefit from built-in open source infrastructure monitoring to keep control of (and optimize) resource usage, health, and performance of database clusters. How Percona Everest is designed Percona Everest has two primary components Percona Everest application with the UI On the frontend, there is a Percona Everest UI which is developed using the Vite framework, React library and TypeScript language. On the backend, requests from the frontend app are processed by Percona Everest Backend. It is an API developed in Golang using the Echo framework that sends requests to the Kubernetes API. You can also use the API directly for your needs. Percona Everest CLI (everestctl) You can install Percona Everest operators and components to your Kubernetes Cluster with the help of a console tool Percona Everest CLI (everestctl). Then, Percona Everest will create and manage database clusters. Everestctl is developed in Golang, and it is a built-in executable file. Percona Everest does not supply a Kubernetes cluster; you’ll need to use your own for the deployment. Percona Everest is currently in Beta We invite you to become an early adopter and contribute to progress! Your feedback is crucial to enhancing the software, and we highly value and rely on your input. Percona Everest website Documentation Quick Start Also published . here

A New Way to Provision Databases on Kubernetes

Introduction In data analysis and time series processing, working with date columns is essential for extracting meaningful insights from datasets. Understanding how to extract specific date components, calculate time differences, set date columns as indexes, and convert date formats are key skills for data analysts and data scientists. In this article, we will explore common scenarios and methods for handling table dates in using the library. Python pandas Extract Date Sometimes we only need to extract year, month, day, and other information from a date in order to better analyze and visualize the data. This can be achieved using the attribute: dt For example, we can extract the year from a date column in a pandas dataframe using the following code: import pandas as pd

# create a sample dataframe with a date column
data = {'date': ['2021-01-10', '2022-05-15', '2023-12-25']}
df = pd.DataFrame(data)

# convert the date column to datetime format
df['date'] = pd.to_datetime(df['date'])

# extract the year from the date column
df['year'] = df['date'].dt.year

print(df) This will output a dataframe with an additional column that contains the extracted year information from the column. You can similarly extract month, day, day of the week, etc. using the attribute in pandas. This allows for more efficient analysis and visualization of time-series data. year date dt Calculate Time Difference In time series analysis, it is common to calculate time differences, such as the number of days or hours between two dates. This can be done using , a function that allows you to perform arithmetic operations on dates and times. timedelta Here is an example of how you can calculate the day/month/year difference in pandas: # Create a dataset
df = pd.DataFrame({'date': ['2019-05-01 10:00:00', '2020-07-12 12:00:00', '2022-08-10 14:00:00'],
                   'value': [1, 2, 3]})

# Convert the date column to datetime format
df['date'] = pd.to_datetime(df['date'])

# Calculate the number of days between two dates
df['days_diff'] = (df['date'] - df['date'].min()).dt.days

# Calculate the number of months between two dates
df['months_diff'] = (df['date'].dt.year - df['date'].min().year) * 12 + (df['date'].dt.month - df['date'].min().month)

# Calculate the number of years between two dates
df['years_diff'] = (df['date'].dt.year - df['date'].min().year)

# Print the dataset
print(df) This code snippet created a DataFrame, converted the 'date' column to DateTime format, and then calculated the number of days, months, and years between each date in the dataset. Set Date Column as Index Setting a date column as the index in pandas is beneficial for time series analysis, filtering, merging datasets, plotting, calculating date-based metrics, and handling time zones. It makes working with time-related data more efficient and accurate. We can use to set the date column as the index. set_index # Create a dataset
df = pd.DataFrame({'date': ['2019-05-01 10:00:00', '2020-07-12 12:00:00', '2022-08-10 14:00:00'],
                   'value': [1, 2, 3]})

# Convert the date column to datetime format
df['date'] = pd.to_datetime(df['date'])

# Set the date column as the index
df = df.set_index('date')

# Print the dataset
print(df) Convert Date Format to String You can use the function to convert date format columns to strings. strftime() In the function, represents the four-digit year, represents the two-digit month, and represents the two-digit day. You can adjust it as needed. strftime() %Y %m %d Here is an example: # How to use strftime

# Create a dataframe containing dates
df = pd.DataFrame({'date': ['2021-10-01', '2021-10-02', '2021-10-03']})

# Convert the date column to datetime format
df['date'] = pd.to_datetime(df['date'])

# Convert the date format column to a string
df['date'] = df['date'].dt.strftime('%Y-%m-%d')

# Print the dataframe
print(df) Convert String to Date Format Option 1: dataframe: String to Date Format - Function pd.to_datetime The function in pandas is used to convert strings or numbers to DateTime format. This function is typically used to convert a time column in the dataset to a DateTime format recognized by pandas for better data analysis and time series analysis. pd.to_datetime() Here is an example using the function: pd.to_datetime() import pandas as pd

# Create a dataframe containing date strings
df = pd.DataFrame({'date': ['2021-10-01', '2021-10-02', '2021-10-03']})

# Convert the date column to datetime format
df['date'] = pd.to_datetime(df['date'])

# Print the dataframe
print(df) Option 2: dataframe: String to Date Format - strptime In addition to the function, you can use the function from the module in Python to convert a string to a date format. pd.to_datetime() datetime.strptime() datetime Here is an example using the function: datetime.strptime() import pandas as pd
from datetime import datetime

# Create a dataframe containing date strings
df = pd.DataFrame({'date_str': ['2021-10-01', '2021-10-02', '2021-10-03']})

# Convert the date column to datetime format
df['date'] = df['date_str'].apply(lambda x: datetime.strptime(x, '%Y-%m-%d'))

# Print the dataframe
print(df) In the function, the first parameter is the string to be converted, and the second parameter is the format of the string. For example, %Y represents the four-digit year, %m represents the two-digit month, and %d represents the two-digit day. You can adjust it as needed. datetime.strptime() The function is more flexible and convenient when dealing with dates, so it is generally recommended to use the function. pd.to_datetime() pd.to_datetime() Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on or (@Luca_DataTeam). You can also catch glimpses of my personal life on , Happy exploring!👋 LinkedIn X Instagram

Python: Effective Techniques for Managing Dates in DataFrame 

Data is powering innovation across every industry. Organizations are racing to build data-driven applications that turn burgeoning information into competitive advantages through actionable insights. However, extracting increasing value from accelerating data volumes poses immense technical challenges. How can applications scale securely while providing real-time analytics? Multi-tenant databases and embedded analytics hold the key. What is a Multi-Tenant Database? A multi-tenant database is a type of database that can store information for multiple users or tenants. Each tenant’s data is kept separate and secure, so they can only access their own information. This allows multiple users or organizations to use the same database while keeping their data private. In the world of cloud computing and Software-as-a-Service (SaaS) business models, the concept of multi-tenant databases is increasingly prominent. The architecture of a multi-tenant database is designed to effectively consolidate shared resources, such as storage and computational power. Why is a Multi-Tenant Database Necessary? This is to efficiently serve multiple customers—each referred to as a tenant. Tenants can be individuals, businesses, or separate organizational units, all sharing the same underlying infrastructure while their data is kept isolated from one another. Through multi-tenancy, SaaS providers are able to maximize resource utilization, optimize maintenance efforts, and streamline updates, which results in significant cost savings. This can also lead to better performance for each tenant as resources are dynamically allocated based on demand. How Does a Multi-Tenant Database Relate to Embedded Analytics? Moving onto embedded analytics, it refers to the integration of reporting tools, data visualizations, and other analytical capabilities directly within business software applications. This approach is distinct from traditional methods that require users to switch between standalone business intelligence tools and business applications to derive insights from data. With , actionable intelligence is brought to users exactly where they need it, within the context of the application they are using. This seamless integration allows users to make data-driven decisions faster and more efficiently. embedded analytics software Embedded analytics is particularly valuable because it democratizes access to data insights. Users without specialized training in data analysis can interpret and utilize complex datasets thanks to intuitive dashboards and reports that are built into the apps they use daily. When multi-tenant databases and embedded analytics come together, the result is powerful: secure, scalable, and performant data solutions that can adapt and grow with increasing customer needs. For companies utilizing these technologies, it means unlocking integrated intelligence capabilities for all users across the platform without compromising security or performance. This synergistic pairing is becoming essential in creating a competitive edge and fostering an environment where continuous learning and improvement through data insights is the norm. Benefits of Multi-Tenant Databases Multi-tenant databases offer compelling advantages but also pose unique design constraints. Resource Pooling and Elastic Scalability Consolidating storage, compute, and administration overhead into shared infrastructure significantly reduces costs for both providers and tenant subscribers as user bases grow. Multi-tenant databases simplify scaling applications horizontally. However, clustered resources must be adequately provisioned to meet demand spikes across the collective tenant base. Tenant Data Isolation While tenants access the same database instance, strict access controls prevent visibility into other tenants’ data. Robust virtual partitioning is enforced at all layers. Maintaining security at scale is paramount. Data leak vulnerabilities breaking these barriers carry tremendous risk. Challenges of Multi-Tenant Databases Customization Constraints When traditional business intelligence providers attempt to integrate with multi-tenant architectures, it restricts tenant-specific customizations to common schemas and configurations. Governance Overhead When SaaS companies attempt to use BI software for multi-tenant reporting, modifying backend data structures impacts all tenant datasets and, therefore, restricts their ability to offer customized data governance or data models per tenant. * * Here is where Qrvey stands out. Qrvey offers a multi-tenant database, or a in reality, that allows for SaaS companies to offer custom data models on a per tenant basis. multi-tenant data warehouse Being multi-tenant ready is a primary reason many companies choose Qrvey for embedded analytics. Maximizing Value with Embedded Analytics Embedding analytics unlocks significant user experience and adoption advantages: Contextual Insights Where They Matter Analytics reach end users directly within regular workflows to guide decisions rather than requiring separate analysis tools. Data-Driven User Experiences Integrated analytics provides self-service visibility so users always know where they stand. Management by metrics at all levels fosters a culture of continuous improvement. Yet, scaling interactive visualizations requires balancing dynamically, freshness, and speed. Analytics Ownership and Collaborative Decision-Making Distributed analytics access helps break down information silos and democratizes data insights for all stakeholders. Shared truth establishes alignment. However, governing massive data access, modeling lineage, and maintaining consistency proves challenging. Uniting Multi-Tenancy and Embedded Analytics Combining multi-tenant platforms and embedded analytics unlocks robust, scalable and cost-efficient solutions to access insights from data at rest and in motion: Scalable Data Infrastructure Multi-tenant databases provide the foundation for managing massive structured and unstructured data volumes across many tenants while controlling costs. Embedded analytics preserves query performance despite increasing user bases. Air-Tight Security Strict tenant isolation secures data, while robust access controls limit users only to specific permitted data subsets and analytics. Granular permissions prevent leakage across tenant barriers. Lower Total Cost of Ownership Shared infrastructure and centralized software instance administration significantly reduce costs for providers. Tenants also enjoy cost savings passed through economies of scale. Customized Insights, Consistent Platform Each tenant can customize analytics models, build dashboards, and tune reports within their space while conforming to shared schemas. Providers standardize platforms and updates. Real-World Use Cases Leveraging Multi-Tenant and Embedded Intelligence Multi-tenant database architecture underpins many leading services providing embedded analytics: Zendesk serves 145,000 accounts with embedded reporting on support interactions leveraging multi-tenancy for scale and security. Asana delivers insights to 100,000+ project collaboration customers built within its multi-tenant database environment. Zuora manages subscription billing analytics across 1000s of platforms through multi-tenant data warehousing. The combinations of scale, security, and insight accessibility provided by multi-tenant databases and embedded analytics solved data challenges that held back application innovation and analytics democratization. What are the Requirements of a Multi-Tenant Database for Embedded Analytics? A multi-tenant database for embedded analytics must meet several key requirements. First, it should have robust data isolation capabilities to ensure that each tenant’s data is securely separated from others. This includes implementing strong access controls and encryption mechanisms. Secondly, the database should support efficient and scalable data storage and retrieval, as it needs to handle large volumes of data from multiple tenants simultaneously. (Read more on for its multi-tenant data warehouse) why Qrvey chose Elastic / AWS OpenSearch Additionally, it should provide flexible data modeling options to accommodate different types of analytics use cases on the tenant level. This includes supporting various data structures such as relational, document, or graph models. Lastly, the database should offer comprehensive reporting and visualization capabilities, allowing tenants to easily generate and share insights from their data using customizable . data visualization tools Overall, a multi-tenant database for embedded analytics needs to prioritize security, scalability, flexibility, and usability to effectively serve the needs of its users. Architecting Tomorrow’s Data-Driven Applications Unlocking value from accelerating data sources is imperative for competitive advantages. Multi-tenant database infrastructure secured with embedded analytics paves the way for customized and scalable data apps, serving many tenants with insights needed at their fingertips. Get started on building or leveraging these technologies by contacting our team for architecture design consulting, troubleshooting current implementations, or technology integration assistance. The future of data-driven innovation powered by multi-tenancy and embedded insights is here with Qrvey. Also published . here

Scalable and Secure Data-Driven Application With Multi-Tenant Databases and Embedded Analytics

মাল্টি-টেন্যান্ট ডেটাবেস এবং এমবেডেড অ্যানালিটিক্স সহ স্কেলেবল এবং সুরক্ষিত ডেটা-চালিত অ্যাপ্লিকেশন

Skalierbare und sichere datengesteuerte Anwendung mit mandantenfähigen Datenbanken und eingebetteter Analyse

Aplicación escalable y segura basada en datos con bases de datos multiinquilino y análisis integrados

Application basée sur les données évolutive et sécurisée avec bases de données multi-locataires et analyses intégrées

मल्टी-टेनेंट डेटाबेस और एंबेडेड एनालिटिक्स के साथ स्केलेबल और सुरक्षित डेटा-संचालित एप्लिकेशन

マルチテナントデータベースと組み込み分析を備えた、スケーラブルで安全なデータ駆動型アプリケーション

다중 테넌트 데이터베이스 및 내장된 분석 기능을 갖춘 확장 가능하고 안전한 데이터 기반 애플리케이션

Aplicativo baseado em dados escalonável e seguro com bancos de dados multilocatários e análises incorporadas

Масштабируемое и безопасное приложение, управляемое данными, с мультитенантными базами данных и встроенной аналитикой

Çok Kiracılı Veritabanları ve Yerleşik Analitiklerle Ölçeklenebilir ve Güvenli Veriye Dayalı Uygulama

Ứng dụng dựa trên dữ liệu an toàn và có thể mở rộng với cơ sở dữ liệu nhiều người thuê và phân tích nhúng

具有多租户数据库和嵌入式分析的可扩展且安全的数据驱动应用程序

Introduction In Python, you can use the pandas library to work with tabular data, and the core data type in pandas is the DataFrame. Sometimes, when working with DataFrame , you may need to convert rows to columns or columns to rows. Here is a simple example demonstrating how to achieve this using the pandas library. data Create Table First, let’s create a new example DataFrame. import pandas as pd

# create a new DataFrame
df = pd.DataFrame({
    'name': ['John', 'Mary', 'Peter'],
    'math': [80, 90, 70],
    'english': [70, 85, 90],
    'science': [75, 95, 80]
})

print(df)

# output:
    name  math  english  science
0   John    80       70       75
1   Mary    90       85       95
2  Peter    70       90       80 Convert Rows to Columns # Use the melt function to convert rows to columns
df_melt = pd.melt(df, id_vars=['name'], var_name='subject', value_name='score')

print(df_melt) In the code above, we simply use the function to transform each student's subject grades into a column, which gives us the following output. melt name  subject  score
0   John     math     80
1   Mary     math     90
2  Peter     math     70
3   John  english     70
4   Mary  english     85
5  Peter  english     90
6   John  science     75
7   Mary  science     95
8  Peter  science     80 Convert Columns to Rows Column-to-row transformation, also known as data unpivoting, can be achieved using the function in the pandas library. Here is an example code: pivot # Use the pivot function to pivot rows to columns.
df_pivot = df_meld.pivot(index='name', columns='subject', values='score')
print(df_pivot) subject  english  math  science
name
John          70    80       75
Mary          85    90       95
Peter         90    70       80 Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on or (@Luca_DataTeam). You can also catch glimpses of my personal life on , Happy exploring!👋 LinkedIn X Instagram

How to Convert Rows to Columns and Columns to Rows in Pandas DataFrame using Python

Introduction Proficiency in database management is an essential skill for data analysts. Oftentimes, colleagues from different departments need quick access to specific datasets, making the traditional method of exporting Excel files from databases inefficient and time-consuming. A more effective approach involves creating new user accounts in the database and granting them appropriate viewing rights. This allows colleagues to seamlessly connect to the database using tools like Power BI or Power Query, enabling them to access necessary data tables promptly and efficiently. This streamlined process fosters collaboration and supports decision-making across organizational departments. data-driven Creating a new user To create a new user in the Oracle database, you can execute the following SQL command: CREATE USER 'USERNAME' IDENTIFIED BY 'PASSWORD'; For example: CREATE USER TEST_USER IDENTIFIED BY TEST123; Granting the Connect Role After creating a new user, attempting to connect may result in an error message: [72000][1045] ORA-01045: user TEST_USER lacks CREATE SESSION privilege; logon denied To enable the user to connect to the database, you must grant the CONNECT role: GRANT CONNECT TO TEST_USER; Merely connecting is not enough - make sure to grant the RESOURCE privilege to allow access to the tables. GRANT CONNECT, RESOURCE TO TEST_USER; Table privilege To grant specific privileges on tables to a user, you can utilize the GRANT command on specific table TO user. For example: GRANT SELECT ON schema.table_name TO TEST_USER; If you wish to provide the user with options beyond selection, such as insertion, updating, and deletion, you may proceed with the execution of the following command. GRANT SELECT, INSERT, UPDATE, DELETE ON schema.table_name TO TEST_USER; Delete a user To remove a user from the database, you can execute the following SQL command: DROP USER TEST_USER; Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on or (@Luca_DataTeam). You can also catch glimpses of my personal life on , Happy exploring!👋 LinkedIn X Instagram Also published . here

Database Management: Creating and Granting User Access in Oracle

Building new software always comes with unknowns. We try to reduce these with research, but there are always unanswered questions about what users truly need. This is where (MVPs) come in. As engineers, we need to strike a balance between overly loose “cowboy architecture” and building with future scale in mind. The database schema of an MVP is particularly critical. It determines the application’s data model, so flexibility is important. Yet, we need enough structure to avoid problems when it’s time to scale. Minimum Viable Products A Three-Step Process for Designing the Right Schema 1. Extrapolate to the Edges To understand an idea, take it to extremes – both big and small. This helps expose where it breaks down. Apply this to your MVP with non-functional requirements: How should the system perform at its absolute design limits? You likely don’t need planet-scale planning, but consider how it should behave once you hit traction. 2. Identify First-Class Objects These are entities with identities independent of others. A common example is a ‘User’. In e-commerce, a ‘Purchase Order’ (PO) might be one.  An ‘Invoice’, however, is dependent on a PO and thus wouldn’t be first-class. 3. Visualize Diagrams are powerful! Create a visual representation of your schema to see how things relate. This exposes potential complexity. Tools like ERD Editor in VSCode work well for MVPs. An Example I’m working on a SaaS app with GitHub-inspired access control for individuals, teams, and organizations. I used this process for my database design: : Defined desired features & performance at scale. Extrapolation : ‘Users’, ‘Organizations’, and ‘Roles’ needed independent existence for flexibility. First-Class Objects : The diagram helped me simplify and spot issues in relationships. Visualization Features/Capabilities Individual user accounts Organization accounts Users can belong to zero or more organizations Team support Organizations can have zero or more teams Role-based access control that is flexible Different roles for users in different organizations Non-Functional Requirements Simple to grok schema Flexible Role-based ACL Be performant at this scale: 1000-5000 Organizations 10-20 Teams per Organization 5-20 User per Team 100,000-500,000 Users Limitations A note on the limitations – for the sake schema simplicity and flexibility I chose to handle the limitations with business logic in code. Organization Owner -  Schema lacks enforcement of an organization “owner” Unique Organization/Team Names - Schema lacks normalization of , Organization.name Team.name schema & Diagram I used ORM to generate the SQL. I’ve shared my schema as a gist: “ .” Take a look for specifics. Prisma Flexible Multi-Tenant Web App Prisma Schema Summary Building a database schema for an MVP that scales well means finding the middle ground between adaptability and thoughtful planning. Follow these steps to get closer to that balance, as the perfect answer at the outset is unlikely. Also published here.

Designing the Right Database Schema for Your MVP Web App: A Three-Step Process

SQL has a long and proven history. It has survived the fuss around NoSQL. And even if not perfect, it has been demonstrated to be the best available language for data. This is no surprise! The story began in the 1960s with the development of databases—an era marked by the introduction of the Integrated Data Store (IDS) at General Electric. However, it was Edgar Codd's relational model that revolutionized data handling. His model, which turned data into a series of tables (or, more strictly, relations), has influenced database systems ever since. This era also saw the birth of SQL (Structured Query Language), which became the standard language for interacting with relational databases, including MariaDB and others. The utility of relational database systems So, why do we need all this database stuff? Let's imagine you're building an app, maybe a simple to-do list to keep track of your daily tasks. Initially, you might think, "Why not just save each task directly to a file?" After all, my programming language has constructs and libraries to save and read data from disk. Also, implementing this seems straightforward: create a task, write it to a file, delete a task, and remove it from the file. These are good points; however, as your app gains traction, users start to aggregate, and suddenly, you have thousands of users trying to add, delete, and modify tasks simultaneously. At this point, the simplicity of files becomes fragile. Imagine one user is updating a task at the exact moment another tries to delete it. Or maybe two users are editing the same task at the same time. With a simple file system, you're likely to end up with corrupted or lost data because there's no inherent mechanism to handle such conflicts. Databases handle these situations gracefully through the . Essentially, a set of principles ensures that even if your app crashes midway through an update, the data remains consistent, and no half-completed tasks are left hanging. Back to the to-do app example, imagine trying to move your task "Buy groceries" from to which also requires changing the property, but your app crashes right in the middle. With a relational database, it's all or nothing—either the task is marked complete and the property reflects the new time value, or it's like you never tried to update it in the first place, avoiding those incorrect half-states. ACID properties pending completed last_updated last_updated Now, let's consider data relationships. In your app, tasks might belong to different categories or users. In a file system, maintaining these relationships is cumbersome. You might end up with a separate file for each category or user, but then how do you quickly find all tasks across categories or ensure two users don't end up with the same task ID? Databases have the ability to manage complex relationships, making it easy to query all tasks for a specific user or category or even more complex queries like "show me the number of completed tasks for user U grouped by category C during the last month." Security is another biggie. In a file system, if someone gains access to your files, they have your data. Databases offer robust security features, like access controls and encryption, safeguarding your data from unauthorized eyes. And then there's the issue of growth. Your simple to-do app might evolve into a complex enterprise project management tool over time. With a file system, every change can feel like renovating a building with people still inside. Databases are built to be flexible and scalable, meaning they're designed to grow with your needs, whether you're adding new features or handling more users. In the end, choosing a database over a simple file system is about preparing for success while standing on solid ground. It's about ensuring that as your app grows, your data remains secure, consistent, and manageable and your users happy. After all, no one likes losing their to-do list to a random crash or waiting forever for their tasks to load because the system is bogged down handling conflicts and searches! A bit of history It was Edgar Codd who proposed the relational model for databases, and since he was a mathematician, he formalized the concepts by creating what is called relational algebra and relational calculus. All this was theoretical until IBM and others started to implement the concepts in academic and research projects. They also wanted to come up with a standard language for querying data in relational databases. At first, they invented QUEL (Querying Using the English Language) at the University of California, Berkeley. At IBM, researchers wanted to come up with their own language and started a project that I perceive more as a game between colleagues called SQUARE (Specifying Queries Using a Relational Environment). This led to a query language that had a scientific-like notation with subindexes and super-indexes, which was hard to type on computer keyboards. To solve this, they redefined the language to only use standard characters and, in an ingenious and probably friendly mockery way, called it SEQUEL. This name, however, was a trademark in the UK, which prevented them from using it. They removed the vowels in SEQUEL, and boom! SQL was born. By 1986, SQL would become an ISO and ANSI standard. As a curious historical remark, although their inventors had to rename SEQUEL to SQL, they continued to call it "sequel." Even today, many software developers and IT professionals continue to pronounce it "sequel." The name Structured Query Language (SQL) would appear later. The utility of SQL SQL is a declarative language, meaning that you specify what you want to get and not how to get it. The database is in charge of doing whatever needs to be done to get the data requested. SQL isolates database complexity. A database is a complex piece of software with tons of algorithms implemented in it. These algorithms deal with different ways to get data stored in disk or memory. Different algorithms are more efficient in different circumstances, which include different queries and different datasets. For example, in MariaDB, a component called the is in charge of deciding what algorithms to use given a SQL query and stats gathered on the actual data. The query optimizer analyzes the SQL query, the data structures, the database schema, and the statistical distribution of the data. It then decides whether to use an index, which joining algorithm is the best, and how to sequence the operations. This process involves a remarkable amount of complexity and mathematical precision, all of which the database abstractly manages for you. As a developer, you only need to worry about constructing the query to get the data you need and let the database figure out whether to use an index or not (with some datasets, not using an index could be faster), B-trees, hash tables, and even whether to add the data to an in-memory cache, as well as many other things. query optimizer SQL also allows you to handle writes, that is, creating and updating data. It also allows you to define the schema of the database, or in short and over-simplifying, the tables and their column structure. In fact, there's much more that SQL allows you to do, and its functionality can be divided into four categories: Creating and manipulating the schema. Data definition language (DDL): Inserting, updating, and deleting data from the database. Data manipulation language (DML): Retrieving data from the database. Data query language (DQL): Dealing with rights and permissions over the database and its objects. Data control language (DCL): In my more than 15 years of experience in the industry, I have rarely seen the previous categories used in a work environment, with the exception of DDL, which refers to activities related to handling database schema updates. These categories are useful mostly in academic circles or in teams implementing relational database management software. However, it's good to know that these terms exist and are used by others as they help in discussions around database technology. With this in mind, let me briefly touch on one of such discussions. Some would say that developers have to deal only with DML and DQL, while DDL and DCL are DBAs' concerns. In practice, this division is not so easy to make. Developers need to understand how database objects (like tables and columns) are created and how access to these objects is managed. However, it is true that developers spend most of their time writing SQL statements to modify and query data. You'll see that this book focuses on DML and DQL while explaining other categories as needed. On the other hand, DBA's are experts on everything database—from infrastructure and general database management to SQL query optimization and migration, a DBA is always a valuable brain to have in your team. Conclusion So, in conclusion, databases solve real problems that application developers face, thanks to their ability to ensure data integrity through ACID properties, manage complex relationships, and provide robust security features. I only scratched the surface here, but this should be enough to give novice IT practitioners a quick refresh on the importance of relational databases and SQL. Also published . here

Why Databases and SQL Matter: The Pillars of Modern App Development

Stories, News & Mentions about DATARADE

Datarade’s Stories on HackerNoon