psql --username=postgres --host=localhost -f

cat /home/project/postgres/data/postgresql.conf

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, run "pg_ctl reload", or execute
# "SELECT pg_reload_conf()".  Some parameters, which are marked below,
# require a server shutdown and restart to take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
#                MB = megabytes                     s   = seconds
#                GB = gigabytes                     min = minutes
#                TB = terabytes                     h   = hours
#                                                   d   = days

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'           # use data in another directory
                                        # (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'     # host-based authentication file
                                        # (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
                                        # (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = ''                 # write an extra PID file
                                        # (change requires restart)

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/var/run/postgresql'        # comma-separated list of dir
ectories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
#bonjour = off                          # advertise server via Bonjour
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)

# - TCP settings -
...

CREATE DATABASE sales ONWER postgres;
\l # database 출력
\c sales # database 선택

CREATE TABLE sales."Customer"
(
    customerid integer NOT NULL,
    category varchar(10) NOT NULL,
    country varchar(40) NOT NULL,
    industry varchar(40) NOT NULL,
    PRIMARY KEY (customerid)
);
\dt # table 출력
\d Customer # describe table

INSERT INTO "Customer"(customerid,category,country,industry)
VALUES
(1,'Individual','Indonesia','Engineering'),
(614,'Individual','United States','Product Management'),
(615,'Individual','China','Services');

# pg_dump -u {username} -W -F p {database명} > {dump파일명}
pg_dump -u postgres -F p sales > sales.sql

# psql -f {dump파일명} {database명}
psql -f sales.sql --username=postgres --host=localhost

cat customer-data.csv | psql --username=postgres -d sales --host=localhost -c "copy sales.customer from STDIN WITH (FORMAT csv);"

CREATE USER user_a WITH PASSWORD 'user_a_password';

] mongo -u root -p password --authenticationDatabase admin local

use mongotest1 # mongotest1 database 사용
show dbs # 생성된 database 확인

db.createCollection("languages") # languages라는 콜렉션(테이블) 생성
db.getCollectionNames()
> [ "languages" ]
db.languages.insert({"name":"java","type":"object oriented"}) # name, type 삽입
db.languages.insert({"name":"python","type":"general purpose"})
db.languages.insert({"name":"scala","type":"functional"})
db.languages.insert({"name":"c","type":"procedural"})
db.languages.insert({"name":"c++","type":"object oriented"})

db.languages.count()
db.languages.findOne() # select * from languages limit 1
db.languages.find() # select * from languages
db.languages.find().limit(3) 
db.languages.find({"name":"python"}) # where name= python
db.languages.find({"type":"object oriented"}) 
db.languages.find({},{"name":1}) # select name from languages
db.languages.find({},{"name":0}) # select name빼고 전부 from languages
db.languages.find({"type":"object oriented"},{"name":1}) # select name from languages where tyep= object oriented
db.languages.remove({"name":"scala"}) # delete from languages where name= scala
db.languages.remove({}) # delete from languages 전부

# 대량으로 데이터 생성
for (i=1;i<=200000;i++){print(i);db.bigdata.insert({"account_no":i,"balance":Math.round(Math.random()*1000000)})}

# index 생성 전 실행계획 확인
db.bigdata.find({"account_no":58982}).explain("executionStats").executionStats.executionTimeMillis
> 82

# index 생성
db.bigdata.createIndex({"account_no":1})
> {
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

db.bigdata.getIndexes()
> [
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "mongotest1.bigdata"
        },
        {
                "v" : 2,
                "key" : {
                        "account_no" : 1
                },
                "name" : "account_no_1",
                "ns" : "mongotest1.bigdata"
        }
]
>

# index 생성 후 실행계획 확인
db.bigdata.find({"account_no":58982}).explain("executionStats").executionStats.executionTimeMillis
> 1

# index 삭제
db.bigdata.dropIndex({"account_no":1})
> { "nIndexesWas" : 2, "ok" : 1 }

db.marks.insert({"name":"Ramesh","subject":"maths","marks":87})
db.marks.insert({"name":"Ramesh","subject":"english","marks":59})
db.marks.insert({"name":"Ramesh","subject":"science","marks":77})
db.marks.insert({"name":"Rav","subject":"maths","marks":62})
db.marks.insert({"name":"Rav","subject":"english","marks":83})
db.marks.insert({"name":"Rav","subject":"science","marks":71})
db.marks.insert({"name":"Alison","subject":"maths","marks":84})
db.marks.insert({"name":"Alison","subject":"english","marks":82})
db.marks.insert({"name":"Alison","subject":"science","marks":86})
db.marks.insert({"name":"Steve","subject":"maths","marks":81})
db.marks.insert({"name":"Steve","subject":"english","marks":89})
db.marks.insert({"name":"Steve","subject":"science","marks":77})
db.marks.insert({"name":"Jan","subject":"english","marks":0,"reason":"absent"})

db.marks.aggregate([{"$limit":1}]) # limit 1
> { "_id" : ObjectId("62344250a953db5edbad218b"), "name" : "Ramesh", "subject" : "maths", "marks" : 87 }
  
db.marks.aggregate([ # order by marks desc, limit 2
... {"$sort":{"marks":-1}},
... {"$limit":2}
... ])
> { "_id" : ObjectId("62344250a953db5edbad2195"), "name" : "Steve", "subject" : "english", "marks" : 89 }
  { "_id" : ObjectId("62344250a953db5edbad218b"), "name" : "Ramesh", "subject" : "maths", "marks" : 87 }
  
db.marks.aggregate([ # select subject as '_id', avg(marks) as 'average' from languages group by subject 
... {
...     "$group":{
...         "_id":"$subject",
...         "average":{"$avg":"$marks"}
...         }
... }
... ])
> { "_id" : "english", "average" : 62.6 }
  { "_id" : "science", "average" : 77.75 }
  { "_id" : "maths", "average" : 78.5 }  
  
db.marks.aggregate([ # 위에 꺼 다합쳐서
... {
...     "$group":{
...         "_id":"$name",
...         "average":{"$avg":"$marks"}
...         }
... },
... {
...     "$sort":{"average":-1}
... },
... {
...     "$limit":2
... }
... ])
> { "_id" : "Alison", "average" : 84 }
  { "_id" : "Steve", "average" : 82.33333333333333 }

] pip install pymongo # mongodb 패키지 설치
] cat mongo_test.py
from pymongo import MongoClient
user = 'root'
password = 'MjQwOTgtcnNhbm5h' # CHANGE THIS TO THE PASSWORD YOU NOTED IN THE EARLIER EXCERCISE - 2
host='localhost'
#create the connection url
connecturl = "mongodb://{}:{}@{}:27017/?authSource=admin".format(user,password,host)

print("Connecting to mongodb server")
connection = MongoClient(connecturl) # 연결

db = connection.mongo_test1 # mongo_test1 database 선택

collection = db.mongodb_glossary # glossary 콜렉션 선택

doc1 = {"database":"a database contains collections"}
doc2 = {"collection":"a collection stores the documents"},
doc3 = {"document":"a document contains the data in the form or key value pairs."}

db.collection.insert(doc1)
db.collection.insert(doc2)
db.collection.insert(doc3)

docs = db.collection.find()

print("Printing the documents in the collection.")
for document in docs:
    print(document)

print("Closing the connection.")
connection.close() # 연결 종료
] python3 mongo_test.py

] wget https://fastdl.mongodb.org/tools/db/mongodb-database-tools-ubuntu1804-x86_64-100.3.1.tgz
] tar -xf mongodb-database-tools-ubuntu1804-x86_64-100.3.1.tgz
] export PATH=$PATH:/home/project/mongodb-database-tools-ubuntu1804-x86_64-100.3.1/bin
] mongoimport --version
mongoimport version: 100.3.1
git version: 32632b931f9c41d8314b75ecc88e551b012b1e30
Go version: go1.15.8
   os: linux
   arch: amd64
   compiler: gc
   
# json to mongoDB import
] mongoimport --file testdata.json --host localhost --port 27000 --db test_mongo1 -collection products -u test-user1 -p test-user1

# mongoDB to csv export
] mongoexport -db test_mongo1 -co testcoll1 --csv --query { test_id:{$exist:true}, xid:{$gt:0}}