Snowflake : Introduction and Architecture

Snowflake is a very trending data warehouse technology due to its benefits and features over traditional database technology, here we will have a brief introduction to snowflake data warehouse and its architecture 

Snowflake is true software as a service provided with an advanced data platform.    

snowflake does not use traditional database technology; it is designed with a new SQL engine with innovative architecture designed for the cloud.

snowflake is a completely cloud-based data warehouse technology 

 

Now we will see why snowflake is a true SaaS (Software as Service) 

• There is no hardware to select, install, configure or manage 
• No software to install, configure or manage
• maintenance, upgrade, and patching all is handled by snowflake itself

 

Snowflake cannot be run on an on-premise or hosted server

There are two main parts of the snowflake 

• Compute - to fulfill the need for resources to compute or to perform operations
• Storage - service to store data

  

Snowflake Architecture 

Snowflake architecture consists of three key layers  

1. Storage 
2. Query processing
3. Cloud services 

 

Database Storage 

The database storage layer is basically to store and manage snowflake data, snowflake reorganizes data into internally optimized, compressed, and columnar formats. snowflake stores data in cloud storage such as AWS, GCP, or Azure.

data objects managed and stored by snowflake not are directly visible or accessible by customers of snowflake

 [Also read -How to get account name in snowflake ]

Query Processing (muscle of the system)

The query processing layer performs the execution of queries, this layer consists of virtual warehouses, Virtual warehouse is a virtual compute resource, that can be used to process all of the queries.

each virtual house is independent of the other, so the performance of one virtual warehouse does not impact the other's performance 

 

Cloud Services (the brain of the system) 

This cloud service layer is the combination of different services that are carried out across snowflake. 

Below are the services managed by this cloud service layer

• access and authentication
• infrastructure management
• metadata management
• query parsing and optimization
• security

[Also read -Setting AWS demo account]

Read More

AWS : Setup free demo account

We have discussed the basics of AWS in a previous article, now let's create an amazon web service free demo account

Pre-requisites 

• A valid email id
• A valid payment option such as a credit or debit card

once you are ready, open https://aws.amazon.com in any web browser and click on Create an AWS account

• On the next screen, put the email id and account name.

• On the next screen, amazon asks to verify the email id, and enter the verification code, once you enter the verification code, you need to set a password

• In the next step, enter the below details

• full name
• phone number
• country
• address
• and tick the terms and conditions. 

• On the next page, we need to enter billing information, such as credit/debit card and your PAN is optional here
• On next page, confirm your identity using a phone text message or phone voice call

• On the next page, select your plan

• Once done, you are all set to use amazon web services

'

now you can simply log in to AWS services using aws.amazon.com and click on sign in option and enter the details

Read More

Flashback query in oracle

You must have heard about the time machine in movies or magazines, just think of an oracle providing the same feature with its databases, and that is called a flashback. 

We can go back in time to query data from the table, of course, there are certain limits to it, but it’s possible, we will learn this with an example. 

Here is the table employee, with 8 rows 

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         8

and this particular snapshot is captured at the below timings 

 

SQL> select sysdate from dual;

 

SYSDATE

-------------------

2022-07-21 23:37:12

 

Now, deleted a couple of rows using the below query

 

SQL> delete from employee where emp_id>305;

 

2 rows deleted.

 

SQL> commit;

 

Commit complete.

 

now we can see number of rows in the table 

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         6

 

so basically, 2 rows are deleted from the table, what if you want to read those 2 deleted rows, you have the option to use the flashback query option, below is the exact format to achieve it

Check the number of rows based on timestamp, as we saw we had 8  rows preset in the table at 23:37 but later we deleted them, based on this timing, check the number of rows

 

SQL> select count(*) from employee as of timestamp TO_DATE('2022-07-21 23:37:12','YYYY-MM-DD HH24:MI:SS');

 

  COUNT(*)

----------

         8

 

see here, we were able to fetch data based on the flashback option, this is just reading, what if you want to insert data back to the table, use the below commands

 

SQL> insert into employee select * from employee as of timestamp TO_DATE('2022-07-21 23:37:12','YYYY-MM-DD HH24:MI:SS') where emp_id>305;

 

2 rows were created.

 

SQL> commit;

 

Commit complete.

 

now check the count in the table, you will be surprised to see, that data is inserted back to the table.

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         8

 

Read More

pfile and spfile in oracle database

 In this short article, we will discuss the server parameter file and initialization parameter file i.e., spfile and pfile

 

pfile - parameter file, is a text-based file, readable and editable with any text editor

spfile- server parameter file, binary file, readable with only some utility and can not be edited.

 

pfile is called the initialization parameter file because this is the first file used to start (initialize) the database, spfile is created later on. 

The default extension of these files is ".ora", in an ASM file name can be with any random or fixed number i.e. spfiletechnodb.1223.343543 (technodb is database name)

The following parameters can be seen in the parameter files

 

• memory parameters such as memory target, sga, pga, shared_pool, java pool, large pool
• database and instance name (can be different)
• parameters related to different physical files such as control file, datafiles, archive files
• processes and session details
• trace file location, audit settings, and many more

 

the very first stage of the database startup is "nomount" and that’s where this parameter file will get read, it will load database memory and process settings and takes the location of control files.

 

The default location of the parameter file is based on the operating system you are using

in Linux/Unix it is located at $ORACLE_HOME/dbs location and for windows operation system it is %ORACLE_HOME%\database

 

Parameter change

Assume your database is running on pfile and you want to change the parameter of the database, then you need to perform the below steps

 

• shutdown the database
• add/change the parameter in pfile
• start the database

 

Now assume your database is running on spfile- then based on condition, you may not at all need to restart the database

• change the parameter using alter system command
• Restart the database if required

 

Read More

using nid to change oracle database SID or oracle database name

In this article, we will discuss using the nid utility to change database sid or database name without dropping and recreating the database, before starting this activity make sure you have a good backup of the database as a rollback strategy.

 current database name:        technodb

new database name:              oracledb

 we need to perform one step after another. 

Step 1: Create pfile from spfile or take backup of pfile or password file

SQL> show parameter pfile

 NAME                  VALUE

-------------------- --------------------------------------

spfile                C:\APP\ORACLE\PRODUCT\11.2.0.4\DBHOME_1\ DATABASE\SPFILETECHNODB.ORA

 

SQL> create pfile from spfile;

File created.

SQL>

Step 2: shutdown the database and again open the database using the startup mount option

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL> startup mount

ORACLE instance started. 

Total System Global Area 3156725760 bytes

Fixed Size                  2179896 bytes

Variable Size            1728056520 bytes

Database Buffers         1409286144 bytes

Redo Buffers               17203200 bytes

Database mounted.

 

Step 3: run nid utility with syntax

nid target=sys/<password>@technodb dbname=oracledb 

C:\app\oracle>nid target=sys/<password>@technodb dbname=oracledb

 

DBNEWID: Release 11.2.0.1.0 - Production on Sat Jul 2 23:10:18 2022

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database TECHNODB (DBID=2727431841)

Connected to server version 11.2.0

Control Files in database:

    C:\APP\ORACLE\ORADATA\TECHNODB\CONTROL01.CTL

    C:\APP\ORACLE\FLASH_RECOVERY_AREA\TECHNODB\CONTROL02.CTL

Change database ID and database name TECHNODB to ORACLEDB? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 2727431841 to 3726858970

Changing database name from TECHNODB to ORACLEDB

    Control File C:\APP\ORACLE\ORADATA\TECHNODB\CONTROL01.CTL - modified

    Control File C:\APP\ORACLE\FLASH_RECOVERY_AREA\TECHNODB\CONTROL02.CTL - modified

    Datafile C:\APP\ORACLE\ORADATA\TECHNODB\SYSTEM01.DB - dbid changed, wrote new name

    Datafile C:\APP\ORACLE\ORADATA\TECHNODB\SYSAUX01.DB - dbid changed, wrote new name

    Datafile C:\APP\ORACLE\ORADATA\TECHNODB\UNDOTBS01.DB - dbid changed, wrote new name

    Datafile C:\APP\ORACLE\ORADATA\TECHNODB\USERS01.DB - dbid changed, wrote new name

    Datafile C:\APP\ORACLE\ORADATA\TECHNODB\EXAMPLE01.DB - dbid changed, wrote new name

    Datafile C:\APP\ORACLE\ORADATA\TECHNODB\TEMP01.DB - dbid changed, wrote new name

    Control File C:\APP\ORACLE\ORADATA\TECHNODB\CONTROL01.CTL - dbid changed, wrote new name

    Control File C:\APP\ORACLE\FLASH_RECOVERY_AREA\TECHNODB\CONTROL02.CTL - dbid changed, wrote new name

    Instance shut down

 

Database name changed to ORACLEDB.

Modify parameter file and generate a new password file before restarting.

Database ID for database ORACLEDB changed to 3726858970.

All previous backups and archived redo logs for this database are unusable.

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Successfully changed database name and ID.

DBNEWID - Completed successfully.

C:\app\oracle>

Step 4: As in above, we can see the database name is changed, and the database is shutdown now we need to change it in spfile as well. Open database in nomount mode and change database name using alter system command

 

C:\app\oracle >sqlplus 

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 2 23:12:19 2022 

Copyright (c) 1982, 2010, Oracle.  All rights reserved. 

Enter user-name: /as sysdba

Connected to an idle instance. 

SQL> startup nomount

ORACLE instance started. 

Total System Global Area 3156725760 bytes

Fixed Size                  2179896 bytes

Variable Size            1728056520 bytes

Database Buffers         1409286144 bytes

Redo Buffers               17203200 bytes

SQL>

 

SQL> alter system set db_name=oracledb scope=spfile;

System altered.

SQL>

 

Step 5: if you are using database on Linux/Unix- change the entry in oratab and if you are using the database on windows, then create a new sid using oradim utility, I have my database on windows, so I have created a new sid in windows services using oradim and delete old sid

 C:\windows\system32>oradim -new -sid oracledb

Instance created.

 

C:\windows\system32>oradim -delete -sid technodb

Instance deleted.

 

C:\windows\system32>

 

Step 6: Now change spfile name to with oracle database name  from spfiletechnodb.ora to spfileoracledb.ora and open the database step-wise as we need to open the database using resetlogs options

SQL> startup nomount

ORACLE instance started. 

Total System Global Area 3156725760 bytes

Fixed Size                  2179896 bytes

Variable Size            1728056520 bytes

Database Buffers         1409286144 bytes

Redo Buffers               17203200 bytes

SQL>

SQL> alter database mount;

Database altered.

SQL> show parameter db_name

NAME                                 TYPE        VALUE

----------------- ----------- ------------------------------

db_name                              string      ORACLEDB

SQL>

SQL> alter database open resetlogs;

 

Step 7: Change entry in the listener.ora and tnsnames.ora and just reload the listener using command

lsnrctl reload LISTENER 

In this way, you can database name has been changed, remember, only the database name can be changed

Read More

Installation of Python 3.10 on windows

This is going to be a short article with just a few steps for the installation of python

Step 1: download python for windows for installation from https://www.python.org/downloads/windows

Step 2: click on .exe file for execution, you will see below the window

click on Add Python 3.10 to PATH as well. then click on Install Now 

Step 3: Monitor installation progress

Step 4: Click on finish /close

Step 4: Verify- open the command prompt and type the command python, it will display the version of python

Read here: Introduction and running the first program 

Read More

Introduction to Python and Writing first program

Python is an open-source programming language, open-source refers to free availability and cost-effectiveness. 

Python is a cross-platform language- it means once the program is written in python, the same code can be executed on any operating system platform such as Windows, Linux, Unix, Mac, etc. python is object-oriented programming.

Python has a very huge library of built-in functions and is easy to learn. Using the python application will be able to connect to any major databases such as MySQL, Oracle, or PostgreSQL. 

Control structure in python is used to control the run of the python program. program is basically a set of instructions provided to the system. The program might be just straight execution or might be skipping a few statements and continuing run or jumping to statement and then again coming back to the original statement. 

Writing the first program and executing it

First of all, you need to have python installed on the machine in order to run python programs. 

you can download installed from https://www.python.org/downloads/windows, you can follow simple instructions from here, once the installation is done, just verify using the command python

C:\Users\shaik>python

Python 3.10.5 (tags/v3.10.5:f377153, Jun  6 2022, 16:14:13) [MSC v.1929 64 bit (AMD64)] on win32

Type "help", "copyright", "credits" or "license" for more information.

>>> 

>>> 

Once you get confirmation that python is installed, write the below code in a text file and save it as hello.py

#This is first program of hello world

print("hellow world!!! :) ") 

Once the writing is done, open command prompt and cd to location when hello.py is kept.

and run the script/program using python hello.py

In this way, you can run the python program on your windows machine.

Read More