Thursday, December 29, 2016

Access HBase from BDA Lite using Oracle BigData SQL

SQL> select * from hbase_datademo where rowkey=1000001451580500;
select * from hbase_datademo where rowkey=1000001451580500
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11504: error from external driver: java.lang.NoClassDefFoundError: org/apache/hadoop/hbase/util/Bytes


[oracle@bigdatalite bigdatasql_config]$ less /tmp/bigdata-log4j.log
16/12/29 11:25:41 INFO xcat.XCat: cluster property com.oracle.bigdata.conf.default.serdepropcacheenabled is true
16/12/29 11:25:41 DEBUG xcat.XCat: access param com.oracle.bigdata.serdepropcache is true
16/12/29 11:25:41 DEBUG hive.XCatHive: XCatHive.open: connecting to the hive metastore...
16/12/29 11:25:41 DEBUG hive.XCatHive: XCatHive.open: connect succeeded
16/12/29 11:25:41 DEBUG hive.XCatHive: XCatHive.open: getTable succeeded
16/12/29 11:25:41 ERROR sql.JXADDescribe: JXADDescribe.getInfo
java.lang.NoClassDefFoundError: org/apache/hadoop/hbase/util/Bytes
        at org.apache.hadoop.hive.hbase.HBaseSerDe.parseColumnsMapping(HBaseSerDe.java:184)
        at org.apache.hadoop.hive.hbase.HBaseSerDeParameters.(HBaseSerDeParameters.java:73)
        at org.apache.hadoop.hive.hbase.HBaseSerDe.initialize(HBaseSerDe.java:117)
        at org.apache.hadoop.hive.serde2.AbstractSerDe.initialize(AbstractSerDe.java:53)
        at org.apache.hadoop.hive.serde2.SerDeUtils.initializeSerDe(SerDeUtils.java:520)
        at org.apache.hadoop.hive.metastore.MetaStoreUtils.getDeserializer(MetaStoreUtils.java:392)
        at org.apache.hadoop.hive.ql.metadata.Table.getDeserializerFromMetaStore(Table.java:273)
        at org.apache.hadoop.hive.ql.metadata.Table.getDeserializer(Table.java:255)
        at org.apache.hadoop.hive.ql.metadata.Table.getCols(Table.java:602)
        at oracle.hadoop.sql.xcat.hive.XCatHiveUtil.extractSchema(XCatHiveUtil.java:205)
        at oracle.hadoop.sql.xcat.hive.XCatHiveUtil.valueOf(XCatHiveUtil.java:276)
        at oracle.hadoop.sql.xcat.hive.XCatHive.open(XCatHive.java:209)
        at oracle.hadoop.sql.JXADDescribe._compile(JXADDescribe.java:491)
        at oracle.hadoop.sql.JXADDescribe.getXaddoc(JXADDescribe.java:621)
        at oracle.hadoop.sql.JXADDescribe._getInfo(JXADDescribe.java:429)
        at oracle.hadoop.sql.JXADDescribe.getInfo(JXADDescribe.java:380)
16/12/29 11:25:41 DEBUG metrics.TaskMetrics: jxad_task: DESCRIBE__GET_XADDOC_REQTYPE, time: 64, bytes: 0
16/12/29 11:25:41 TRACE sql.JXADProvider: Entering kubsagtiJNIConvCB...
16/12/29 11:25:41 TRACE sql.JXADProvider: Leaving kubsagtiJNIConvCB...retSize=0
16/12/29 11:25:41 TRACE sql.JXADProvider: java.lang.NoClassDefFoundError: org/apache/hadoop/hbase/util/Bytes
16/12/29 11:25:41 ERROR sql.JXADProvider: KUP-11504: error from external driver: java.lang.NoClassDefFoundError: org/apache/hadoop/hbase/util/Bytes
16/12/29 11:25:41 TRACE sql.JXADProvider: Leaving kubsjniDescribeGetInfo...status=6
16/12/29 11:25:41 TRACE sql.JXADProvider: Leaving kubsagtiDescribeGetInfo...status=33
16/12/29 11:25:41 TRACE sql.JXADProvider: Leaving kubsagtDescribeGetInfo...status=33
16/12/29 11:25:41 DEBUG sql.JXADProvider: using agent handle=79
16/12/29 11:25:41 DEBUG sql.JXADProvider: using agent context=0xedfe98
16/12/29 11:25:41 DEBUG sql.JXADProvider: using agent jvm pointer=0x7fe020bf1b48
16/12/29 11:25:41 DEBUG sql.JXADProvider: using agent jni handle=0x10009f8

SQL> select * from hbase_datademo where rownum<10 p="">select * from hbase_datademo where rownum<10 p="">              *
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11504: error from external driver:
oracle.hadoop.sql.xcat.common.XCatException : 1001 : Error getting splits
document. Cause : java.io.IOException:
java.lang.reflect.InvocationTargetException

16/12/29 13:40:15 DEBUG hadoop.XCatInfm: listStatus(final) file =hdfs://bigdatalite.localdomain:8020/user/hive/warehouse/hbase_datademo
16/12/29 13:40:15 ERROR sql.JXADDescribe: JXADDescribe.getInfo
oracle.hadoop.sql.xcat.common.XCatException : 1001 : Error getting splits document. Cause : java.io.IOException: java.lang.reflect.InvocationTargetException
        at oracle.hadoop.sql.xcat.XCatInfo.getSplitsDoc(XCatInfo.java:395)
        at oracle.hadoop.sql.JXADDescribe.getSplitsDoc(JXADDescribe.java:545)
        at oracle.hadoop.sql.JXADDescribe._getInfo(JXADDescribe.java:417)
        at oracle.hadoop.sql.JXADDescribe.getInfo(JXADDescribe.java:380)
Caused by: java.io.IOException: java.lang.reflect.InvocationTargetException
        at org.apache.hadoop.hbase.client.ConnectionFactory.createConnection(ConnectionFactory.java:240)
        at org.apache.hadoop.hbase.client.ConnectionFactory.createConnection(ConnectionFactory.java:218)
        at org.apache.hadoop.hbase.client.ConnectionFactory.createConnection(ConnectionFactory.java:119)
        at org.apache.hadoop.hive.hbase.HiveHBaseTableInputFormat.getSplitsInternal(HiveHBaseTableInputFormat.java:463)
        at org.apache.hadoop.hive.hbase.HiveHBaseTableInputFormat.getSplits(HiveHBaseTableInputFormat.java:450)
        at oracle.hadoop.sql.xcat.hadoop.mapred.XCatMapredInfm.computeBaseSplits(XCatMapredInfm.java:169)
        at oracle.hadoop.sql.xcat.hadoop.XCatGetSplits.computeSplits(XCatGetSplits.java:563)
        at oracle.hadoop.sql.xcat.XCatInfo.getSplits(XCatInfo.java:354)
        at oracle.hadoop.sql.xcat.XCatInfo.getSplitsDoc(XCatInfo.java:384)
        ... 3 more
Caused by: java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at org.apache.hadoop.hbase.client.ConnectionFactory.createConnection(ConnectionFactory.java:238)
        ... 11 more
Caused by: java.lang.NoClassDefFoundError: org/apache/htrace/Trace
        at org.apache.hadoop.hbase.zookeeper.RecoverableZooKeeper.exists(RecoverableZooKeeper.java:217)
        at org.apache.hadoop.hbase.zookeeper.ZKUtil.checkExists(ZKUtil.java:419)
        at org.apache.hadoop.hbase.zookeeper.ZKClusterId.readClusterIdZNode(ZKClusterId.java:65)
        at org.apache.hadoop.hbase.client.ZooKeeperRegistry.getClusterId(ZooKeeperRegistry.java:105)
        at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.retrieveClusterId(ConnectionManager.java:919)
        at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.(ConnectionManager.java:657)
        ... 16 more
16/12/29 13:40:15 DEBUG metrics.TaskMetrics: jxad_task: DESCRIBE__GET_SPLITS_DOC_REQTYPE, time: 59, bytes: 0
16/12/29 13:40:15 TRACE sql.JXADProvider: Entering kubsagtiJNIConvCB...

How to Fix: Adding Hbase and Hbase Lib to java class path for hadoop inside bigdata.properties file

[oracle@bigdatalite tmp]$ diff /u01/bigdatasql_config/bigdata.properties /u01/bigdatasql_config/bigdata.properties.orig
3c3
< java.classpath.hadoop=/usr/lib/hadoop/client/*:/usr/lib/hadoop-mapreduce/*:/usr/lib/hadoop-mapreduce/lib/*:/usr/lib/hbase/*:/usr/lib/hbase/lib/*
---
> java.classpath.hadoop=/usr/lib/hadoop/client/*:/usr/lib/hadoop-mapreduce/*:/usr/lib/hadoop-mapreduce/lib/*

--- restart BDA Lite

Thursday, November 24, 2016

MongoDB - Change audit filters to audit specific user and DML auditing



# Setup directory for replication set auditing testing (audit for specific user using filter)
# hostname: database.dbaglobe.com
mkdir -p /home/donghua/LAB7/{r0,r1,r2}

# Prepare configure file: /home/donghua/LAB7/r0/mongod.conf
net:
   port: 22700
storage:
   dbPath: /home/donghua/LAB7/r0
systemLog:
   destination: file
   path: /home/donghua/LAB7/r0/mongo.log
   logAppend: true
processManagement:
   fork: true
replication:
   replSetName: AuditTest
#security:
#   authorization: enabled
auditLog:
   destination: file
   format: JSON
   path: /home/donghua/LAB7/r0/auditLog.json
   filter: '{ users: { user: "superuser", db: "admin" } }'
# enable DML auditing by audit atype:authCheck
setParameter: {auditAuthorizationSuccess: true}

# Prepare configure file: /home/donghua/LAB7/r1/mongod.conf
net:
   port: 22701
storage:
   dbPath: /home/donghua/LAB7/r1
systemLog:
   destination: file
   path: /home/donghua/LAB7/r1/mongo.log
   logAppend: true
processManagement:
   fork: true
replication:
   replSetName: AuditTest
#security:
#   authorization: enabled  
auditLog:
   destination: file
   format: JSON
   path: /home/donghua/LAB7/r1/auditLog.json
   filter: '{ users: { user: "superuser", db: "admin" } }'
# enable DML auditing by audit atype:authCheck
setParameter: {auditAuthorizationSuccess: true}

# Prepare configure file: /home/donghua/LAB7/r2/mongod.conf
net:
   port: 22702
storage:
   dbPath: /home/donghua/LAB7/r2
systemLog:
   destination: file
   path: /home/donghua/LAB7/r2/mongo.log
   logAppend: true
processManagement:
   fork: true
replication:
   replSetName: AuditTest
#security:
#   authorization: enabled  
auditLog:
   destination: file
   format: JSON
   path: /home/donghua/LAB7/r2/auditLog.json
   filter: '{ users: { user: "superuser", db: "admin" } }'
# enable DML auditing by audit atype:authCheck
setParameter: {auditAuthorizationSuccess: true}
  
# Setup replset with 3 replicas with auditing enable to JSON File
mongod --config /home/donghua/LAB7/r0/mongod.conf
mongod --config /home/donghua/LAB7/r1/mongod.conf
mongod --config /home/donghua/LAB7/r2/mongod.conf

mongo --port 22700 --eval "rs.initiate({_id: 'AuditTest',members: [{ _id: 1, host: 'database.dbaglobe.com:22700' },{ _id: 2, host: 'database.dbaglobe.com:22701' },{ _id: 3, host: 'database.dbaglobe.com:22702' }]})"
mongo --port 22700 --eval "rs.status()"

# Create application specific user
MongoDB Enterprise AuditTest:PRIMARY> use admin
MongoDB Enterprise AuditTest:PRIMARY> db.createUser( {user: "superuser", pwd: "secret", roles:[{role:'root',db:'admin'}]});

# Shutdown and enable security.authorization
# And restart Mongodb

# Shutdown and clean up
mongod --dbpath /home/donghua/LAB7/r0 --shutdown
mongod --dbpath /home/donghua/LAB7/r1 --shutdown
mongod --dbpath /home/donghua/LAB7/r2 --shutdown
rm -rf /home/donghua/LAB7

MongoDB - Enable auditing on replica set



# Setup directory for replication set testing
# hostname: database.dbaglobe.com
mkdir -p /home/donghua/LAB6/{r0,r1,r2}

# Setup replset with 3 replicas with auditing enable to JSON File
mongod --dbpath /home/donghua/LAB6/r0 --logpath /home/donghua/LAB6/r0/mongo.log --port 3310 --replSet TO_BE_SECURED --fork --auditDestination file --auditFormat JSON --auditPath /home/donghua/LAB6/r0/auditLog.json
mongod --dbpath /home/donghua/LAB6/r1 --logpath /home/donghua/LAB6/r1/mongo.log --port 3311 --replSet TO_BE_SECURED --fork --auditDestination file --auditFormat JSON --auditPath /home/donghua/LAB6/r1/auditLog.json
mongod --dbpath /home/donghua/LAB6/r2 --logpath /home/donghua/LAB6/r2/mongo.log --port 3312 --replSet TO_BE_SECURED --fork --auditDestination file --auditFormat JSON --auditPath /home/donghua/LAB6/r2/auditLog.json

mongo --port 3310 --eval "rs.initiate({_id: 'TO_BE_SECURED',members: [{ _id: 1, host: 'database.dbaglobe.com:3310' },{ _id: 2, host: 'database.dbaglobe.com:3311' },{ _id: 3, host: 'database.dbaglobe.com:3312' }]})"
mongo --port 3310 --eval "rs.status()"

# Shutdown and clean up
mongod --dbpath /home/donghua/LAB6/r0 --shutdown
mongod --dbpath /home/donghua/LAB6/r1 --shutdown
mongod --dbpath /home/donghua/LAB6/r2 --shutdown
rm -rf /home/donghua/LAB6

Thursday, November 17, 2016

Install SQL Server (vNext) on Ubuntu



donghua@ubuntu:~$ lsb_release -d
Description:    Ubuntu 16.04.1 LTS

# Registery GPG key and SQL Server Ubuntu repository:

root@ubuntu:~# curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   983  100   983    0     0   1952      0 --:--:-- --:--:-- --:--:--  1954
OK
root@ubuntu:~# curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list > /etc/apt/sources.list.d/mssql-server.list
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    87  100    87    0     0    545      0 --:--:-- --:--:-- --:--:--   547
root@ubuntu:~# curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/msprod.list
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    79  100    79    0     0    109      0 --:--:-- --:--:-- --:--:--   109

donghua@ubuntu:~$ sudo apt-get update

# Install SQL Server
donghua@ubuntu:~$ sudo apt-get install -y mssql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  linux-headers-4.4.0-31 linux-headers-4.4.0-31-generic linux-image-4.4.0-31-generic linux-image-extra-4.4.0-31-generic
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  libc++1 libjemalloc1
Suggested packages:
  clang
The following NEW packages will be installed:
  libc++1 libjemalloc1 mssql-server
0 upgraded, 3 newly installed, 0 to remove and 55 not upgraded.
Need to get 144 MB of archives.
After this operation, 682 MB of additional disk space will be used.
Get:1 https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial/main amd64 mssql-server amd64 14.0.1.246-6 [144 MB]
Get:2 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 libc++1 amd64 3.7.0-1 [226 kB]
Get:3 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 libjemalloc1 amd64 3.6.0-9ubuntu1 [78.9 kB]
Fetched 144 MB in 32s (4,453 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libc++1:amd64.
(Reading database ... 242891 files and directories currently installed.)
Preparing to unpack .../libc++1_3.7.0-1_amd64.deb ...
Unpacking libc++1:amd64 (3.7.0-1) ...
Selecting previously unselected package libjemalloc1.
Preparing to unpack .../libjemalloc1_3.6.0-9ubuntu1_amd64.deb ...
Unpacking libjemalloc1 (3.6.0-9ubuntu1) ...
Selecting previously unselected package mssql-server.
Preparing to unpack .../mssql-server_14.0.1.246-6_amd64.deb ...
Unpacking mssql-server (14.0.1.246-6) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libc++1:amd64 (3.7.0-1) ...
Setting up libjemalloc1 (3.6.0-9ubuntu1) ...
Setting up mssql-server (14.0.1.246-6) ...

+-------------------------------------------------------------------+
| Please run /opt/mssql/bin/sqlservr-setup to complete the setup of |
|                  Microsoft(R) SQL Server(R).                      |
+-------------------------------------------------------------------+

Processing triggers for libc-bin (2.23-0ubuntu3) ...


# Configure SQL Server
donghua@ubuntu:~$ sudo /opt/mssql/bin/sqlservr-setup
Microsoft(R) SQL Server(R) Setup

You can abort setup at anytime by pressing Ctrl-C. Start this program
with the --help option for information about running it in unattended
mode.

The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746388 and found
in /usr/share/doc/mssql-server/LICENSE.TXT.

Do you accept the license terms? If so, please type "YES": YES

Please enter a password for the system administrator (SA) account:myPassowrd
Please confirm the password for the system administrator (SA) account: myPassowrd

Setting system administrator (SA) account password...

Do you wish to start the SQL Server service now? [y/n]: y
Do you wish to enable SQL Server to start on boot? [y/n]: y
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /lib/systemd/system/mssql-server.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server-telemetry.service to /lib/systemd/system/mssql-server-telemetry.service.

Setup completed successfully.

# Check the SQL Server status

donghua@ubuntu:~$ systemctl status mssql-server
● mssql-server.service - Microsoft(R) SQL Server(R) Database Engine
   Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2016-11-17 23:33:57 SGT; 18s ago
 Main PID: 26336 (sqlservr)
   CGroup: /system.slice/mssql-server.service
           ├─26336 /opt/mssql/bin/sqlservr
           └─26350 /opt/mssql/bin/sqlservr

Nov 17 23:34:00 ubuntu sqlservr[26336]: [62B blob data]
Nov 17 23:34:00 ubuntu sqlservr[26336]: [61B blob data]
Nov 17 23:34:00 ubuntu sqlservr[26336]: [122B blob data]
Nov 17 23:34:00 ubuntu sqlservr[26336]: [145B blob data]
Nov 17 23:34:01 ubuntu sqlservr[26336]: [66B blob data]
Nov 17 23:34:01 ubuntu sqlservr[26336]: [75B blob data]
Nov 17 23:34:01 ubuntu sqlservr[26336]: [96B blob data]
Nov 17 23:34:01 ubuntu sqlservr[26336]: [100B blob data]
Nov 17 23:34:01 ubuntu sqlservr[26336]: [71B blob data]
Nov 17 23:34:01 ubuntu sqlservr[26336]: [124B blob data]

donghua@ubuntu:~$ ps -ef|grep mssql
mssql     26336      1  0 23:33 ?        00:00:00 /opt/mssql/bin/sqlservr
mssql     26341      1  0 23:33 ?        00:00:00 /opt/mssql/bin/sqlservr-telemetry /var/opt/mssql/.system
mssql     26350  26336  6 23:33 ?        00:00:04 /opt/mssql/bin/sqlservr


# Install SQL Server Client Tools

donghua@ubuntu:~$ sudo apt-get install mssql-tools
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  linux-headers-4.4.0-31 linux-headers-4.4.0-31-generic linux-image-4.4.0-31-generic linux-image-extra-4.4.0-31-generic
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  libgmp-dev libgmpxx4ldbl libgnutls-dev libgnutls28-dev libgnutlsxx28 libgss3 libidn11-dev libodbc1-utf16 libp11-kit-dev libshishi-dev libshishi0 libtasn1-6-dev libtasn1-doc msodbcsql nettle-dev
  odbcinst-utf16 odbcinst1debian2-utf16 shishi-common unixodbc-utf16 zlib1g-dev
Suggested packages:
  gmp-doc libgmp10-doc libmpfr-dev gnutls-doc gnutls-bin guile-gnutls shishi libmyodbc odbc-postgresql tdsodbc
The following NEW packages will be installed:
  libgmp-dev libgmpxx4ldbl libgnutls-dev libgnutls28-dev libgnutlsxx28 libgss3 libidn11-dev libodbc1-utf16 libp11-kit-dev libshishi-dev libshishi0 libtasn1-6-dev libtasn1-doc msodbcsql mssql-tools
  nettle-dev odbcinst-utf16 odbcinst1debian2-utf16 shishi-common unixodbc-utf16 zlib1g-dev
0 upgraded, 21 newly installed, 0 to remove and 55 not upgraded.
Need to get 8,431 kB of archives.
After this operation, 12.7 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 https://packages.microsoft.com/ubuntu/16.04/prod xenial/main amd64 libodbc1-utf16 amd64 2.3.1-2 [159 kB]
Get:2 https://packages.microsoft.com/ubuntu/16.04/prod xenial/main amd64 odbcinst1debian2-utf16 amd64 2.3.1-2 [40.8 kB]
Get:3 https://packages.microsoft.com/ubuntu/16.04/prod xenial/main amd64 odbcinst-utf16 amd64 2.3.1-2 [20.7 kB]
Get:4 https://packages.microsoft.com/ubuntu/16.04/prod xenial/main amd64 unixodbc-utf16 amd64 2.3.1-2 [31.3 kB]
Get:5 https://packages.microsoft.com/ubuntu/16.04/prod xenial/main amd64 msodbcsql amd64 13.0.1.0-1 [3,510 kB]
Get:6 http://us.archive.ubuntu.com/ubuntu xenial/main amd64 libgmpxx4ldbl amd64 2:6.1.0+dfsg-2 [8,948 B]
Get:7 https://packages.microsoft.com/ubuntu/16.04/prod xenial/main amd64 mssql-tools amd64 14.0.1.246-1 [1,263 kB]
Get:8 http://us.archive.ubuntu.com/ubuntu xenial/main amd64 libgmp-dev amd64 2:6.1.0+dfsg-2 [314 kB]
Get:9 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libgnutlsxx28 amd64 3.4.10-4ubuntu1.1 [13.7 kB]
Get:10 http://us.archive.ubuntu.com/ubuntu xenial/main amd64 nettle-dev amd64 3.2-1 [937 kB]
Get:11 http://us.archive.ubuntu.com/ubuntu xenial/main amd64 zlib1g-dev amd64 1:1.2.8.dfsg-2ubuntu4 [168 kB]
Get:12 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libtasn1-6-dev amd64 4.7-3ubuntu0.16.04.1 [85.8 kB]
Get:13 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libp11-kit-dev amd64 0.23.2-5~ubuntu16.04.1 [56.5 kB]
Get:14 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libidn11-dev amd64 1.32-3ubuntu1.1 [519 kB]
Get:15 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libgnutls-dev amd64 3.4.10-4ubuntu1.1 [553 kB]
Get:16 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libgnutls28-dev amd64 3.4.10-4ubuntu1.1 [8,164 B]
Get:17 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 shishi-common all 1.0.2-6build1 [60.6 kB]
Get:18 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 libshishi0 amd64 1.0.2-6build1 [159 kB]
Get:19 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 libshishi-dev amd64 1.0.2-6build1 [157 kB]
Get:20 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 libgss3 amd64 1.0.3-2 [65.2 kB]
Get:21 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libtasn1-doc all 4.7-3ubuntu0.16.04.1 [299 kB]
Fetched 8,431 kB in 29s (287 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libgmpxx4ldbl:amd64.
(Reading database ... 242940 files and directories currently installed.)
Preparing to unpack .../libgmpxx4ldbl_2%3a6.1.0+dfsg-2_amd64.deb ...
Unpacking libgmpxx4ldbl:amd64 (2:6.1.0+dfsg-2) ...
Selecting previously unselected package libgmp-dev:amd64.
Preparing to unpack .../libgmp-dev_2%3a6.1.0+dfsg-2_amd64.deb ...
Unpacking libgmp-dev:amd64 (2:6.1.0+dfsg-2) ...
Selecting previously unselected package libgnutlsxx28:amd64.
Preparing to unpack .../libgnutlsxx28_3.4.10-4ubuntu1.1_amd64.deb ...
Unpacking libgnutlsxx28:amd64 (3.4.10-4ubuntu1.1) ...
Selecting previously unselected package nettle-dev.
Preparing to unpack .../nettle-dev_3.2-1_amd64.deb ...
Unpacking nettle-dev (3.2-1) ...
Selecting previously unselected package zlib1g-dev:amd64.
Preparing to unpack .../zlib1g-dev_1%3a1.2.8.dfsg-2ubuntu4_amd64.deb ...
Unpacking zlib1g-dev:amd64 (1:1.2.8.dfsg-2ubuntu4) ...
Selecting previously unselected package libtasn1-6-dev:amd64.
Preparing to unpack .../libtasn1-6-dev_4.7-3ubuntu0.16.04.1_amd64.deb ...
Unpacking libtasn1-6-dev:amd64 (4.7-3ubuntu0.16.04.1) ...
Selecting previously unselected package libp11-kit-dev:amd64.
Preparing to unpack .../libp11-kit-dev_0.23.2-5~ubuntu16.04.1_amd64.deb ...
Unpacking libp11-kit-dev:amd64 (0.23.2-5~ubuntu16.04.1) ...
Selecting previously unselected package libidn11-dev.
Preparing to unpack .../libidn11-dev_1.32-3ubuntu1.1_amd64.deb ...
Unpacking libidn11-dev (1.32-3ubuntu1.1) ...
Selecting previously unselected package libgnutls-dev:amd64.
Preparing to unpack .../libgnutls-dev_3.4.10-4ubuntu1.1_amd64.deb ...
Unpacking libgnutls-dev:amd64 (3.4.10-4ubuntu1.1) ...
Selecting previously unselected package libgnutls28-dev:amd64.
Preparing to unpack .../libgnutls28-dev_3.4.10-4ubuntu1.1_amd64.deb ...
Unpacking libgnutls28-dev:amd64 (3.4.10-4ubuntu1.1) ...
Selecting previously unselected package libodbc1-utf16:amd64.
Preparing to unpack .../libodbc1-utf16_2.3.1-2_amd64.deb ...
Unpacking libodbc1-utf16:amd64 (2.3.1-2) ...
Selecting previously unselected package odbcinst1debian2-utf16:amd64.
Preparing to unpack .../odbcinst1debian2-utf16_2.3.1-2_amd64.deb ...
Unpacking odbcinst1debian2-utf16:amd64 (2.3.1-2) ...
Selecting previously unselected package odbcinst-utf16.
Preparing to unpack .../odbcinst-utf16_2.3.1-2_amd64.deb ...
Unpacking odbcinst-utf16 (2.3.1-2) ...
Selecting previously unselected package unixodbc-utf16.
Preparing to unpack .../unixodbc-utf16_2.3.1-2_amd64.deb ...
Unpacking unixodbc-utf16 (2.3.1-2) ...
Selecting previously unselected package shishi-common.
Preparing to unpack .../shishi-common_1.0.2-6build1_all.deb ...
Unpacking shishi-common (1.0.2-6build1) ...
Selecting previously unselected package libshishi0.
Preparing to unpack .../libshishi0_1.0.2-6build1_amd64.deb ...
Unpacking libshishi0 (1.0.2-6build1) ...
Selecting previously unselected package libshishi-dev.
Preparing to unpack .../libshishi-dev_1.0.2-6build1_amd64.deb ...
Unpacking libshishi-dev (1.0.2-6build1) ...
Selecting previously unselected package libgss3.
Preparing to unpack .../libgss3_1.0.3-2_amd64.deb ...
Unpacking libgss3 (1.0.3-2) ...
Selecting previously unselected package libtasn1-doc.
Preparing to unpack .../libtasn1-doc_4.7-3ubuntu0.16.04.1_all.deb ...
Unpacking libtasn1-doc (4.7-3ubuntu0.16.04.1) ...
Selecting previously unselected package msodbcsql.
Preparing to unpack .../msodbcsql_13.0.1.0-1_amd64.deb ...
Unpacking msodbcsql (13.0.1.0-1) ...
Selecting previously unselected package mssql-tools.
Preparing to unpack .../mssql-tools_14.0.1.246-1_amd64.deb ...
Unpacking mssql-tools (14.0.1.246-1) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Processing triggers for doc-base (0.10.7) ...
Processing 3 added doc-base files...
Processing triggers for install-info (6.1.0.dfsg.1-5) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libgmpxx4ldbl:amd64 (2:6.1.0+dfsg-2) ...
Setting up libgmp-dev:amd64 (2:6.1.0+dfsg-2) ...
Setting up libgnutlsxx28:amd64 (3.4.10-4ubuntu1.1) ...
Setting up nettle-dev (3.2-1) ...
Setting up zlib1g-dev:amd64 (1:1.2.8.dfsg-2ubuntu4) ...
Setting up libtasn1-6-dev:amd64 (4.7-3ubuntu0.16.04.1) ...
Setting up libp11-kit-dev:amd64 (0.23.2-5~ubuntu16.04.1) ...
Setting up libidn11-dev (1.32-3ubuntu1.1) ...
Setting up libgnutls-dev:amd64 (3.4.10-4ubuntu1.1) ...
Setting up libgnutls28-dev:amd64 (3.4.10-4ubuntu1.1) ...
Setting up libodbc1-utf16:amd64 (2.3.1-2) ...
Setting up shishi-common (1.0.2-6build1) ...
Setting up libshishi0 (1.0.2-6build1) ...
Setting up libshishi-dev (1.0.2-6build1) ...
Setting up libgss3 (1.0.3-2) ...
Setting up libtasn1-doc (4.7-3ubuntu0.16.04.1) ...
Setting up odbcinst1debian2-utf16:amd64 (2.3.1-2) ...
Setting up odbcinst-utf16 (2.3.1-2) ...
Setting up unixodbc-utf16 (2.3.1-2) ...
Setting up msodbcsql (13.0.1.0-1) ...
Setting up mssql-tools (14.0.1.246-1) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...

donghua@ubuntu:~$ sqlcmd -S localhost -U SA -P myPassword
1>  SELECT Name,state_desc from sys.Databases
2> go
Name                state_desc
------------------- --------------
master              ONLINE
tempdb              ONLINE
model               ONLINE
msdb                ONLINE

(4 rows affected)
1> create database test
2> go
1> use TEST
2> go
Changed database context to 'test'.
1> CREATE TABLE inventory (id INT, name NVARCHAR(50), quantity INT)
2> go
1> INSERT INTO inventory VALUES (1, 'banana', 150);
2> INSERT INTO inventory VALUES (2, 'orange', 154);
3> go

(1 rows affected)

(1 rows affected)
1> select * from inventory;
2> go
id          name                                               quantity
----------- -------------------------------------------------- -----------
          1 banana                                                     150
          2 orange                                                     154

(2 rows affected)
1> select @@version;
2> go
                                                                                                                                                                                                              
-----------------------------------------------------
Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)
        Nov  1 2016 23:24:39
        Copyright (c) Microsoft Corporation
        on Linux (Ubuntu 16.04.1 LTS)

(1 rows affected)

root@ubuntu:/opt/mssql/bin# ls -l /opt/mssql/bin /opt/mssql-tools/bin
/opt/mssql/bin:
total 1756
-rwxr-xr-x 1 root root    801 Nov 15 07:03 compress-dump.sh
-rwxr-xr-x 1 root root   5197 Nov 15 07:03 generate-core.sh
-rwxr-xr-x 1 root root     73 Nov 15 07:02 mssql-conf
-rwxr-xr-x 1 root root 590360 Nov 15 07:03 sqlpackage
-rwxr-xr-x 1 root root 591224 Nov 15 07:03 sqlservr
-rwxr-xr-x 1 root root   9615 Nov 15 07:02 sqlservr-setup
-rwxr-xr-x 1 root root 581560 Nov 15 07:03 sqlservr-telemetry

/opt/mssql-tools/bin:
total 5620
-rwxrwxr-x 1 root root 1114264 Nov  9 12:23 bcp-14.0.1.246
-rwxrwxr-x 1 root root 4635376 Nov  9 12:23 sqlcmd-14.0.1.246


root@ubuntu:/var/opt/mssql/data# ls -l /var/opt/mssql/data
total 69440
-rw-r----- 1 mssql mssql  4194304 Nov 17 23:39 master.mdf
-rw-r----- 1 mssql mssql  2097152 Nov 17 23:41 mastlog.ldf
-rw-r----- 1 mssql mssql  8388608 Nov 17 23:41 modellog.ldf
-rw-r----- 1 mssql mssql  8388608 Nov 17 23:41 model.mdf
-rw-r----- 1 mssql mssql 13959168 Nov 17 23:34 msdbdata.mdf
-rw-r----- 1 mssql mssql   524288 Nov 17 23:34 msdblog.ldf
-rw-rw---- 1 mssql mssql  8388608 Nov 17 23:34 tempdb.mdf
-rw-rw---- 1 mssql mssql  8388608 Nov 17 23:39 templog.ldf
-rw-rw---- 1 mssql mssql  8388608 Nov 17 23:42 test_log.ldf
-rw-rw---- 1 mssql mssql  8388608 Nov 17 23:41 test.mdf

root@ubuntu:/var/opt/mssql/data# ls -l /var/opt/mssql/log/
total 2508
-rw-rw---- 1 mssql mssql   14696 Nov 17 23:41 errorlog
-rw-r----- 1 mssql mssql   11780 Nov 17 23:33 errorlog.1
-rw-r----- 1 mssql mssql       0 Nov 17 23:33 errorlog.2
-rw-r----- 1 mssql mssql   77824 Nov 17 23:33 HkEngineEventFile_0_131238704318540000.xel
-rw-rw---- 1 mssql mssql   77824 Nov 17 23:33 HkEngineEventFile_0_131238704394410000.xel
-rw-rw---- 1 mssql mssql 1048576 Nov 17 23:39 log_1.trc
-rw-rw---- 1 mssql mssql   12288 Nov 17 23:41 log_2.trc
-rw-r----- 1 mssql mssql 1048576 Nov 17 23:33 log.trc
-rw-r--r-- 1 mssql mssql    6902 Nov 17 23:33 setup-20161117-233347.log
-rw-r----- 1 mssql mssql  102400 Nov 17 23:33 system_health_0_131238704326830000.xel
-rw-rw---- 1 mssql mssql  163840 Nov 17 23:47 system_health_0_131238704398150000.xel

Reference URL: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup