By digoal
The Babelfish Compass (short for COMPatibility ASSessment) tool analyzes SQL/DDL code for one or more Microsoft SQL Server databases to identify the SQL features incompatible with Babelfish for PostgreSQL.
SQL Server users can use Babelfish Compass to analyze the SQL/DDL code for their current SQL Server-based applications for compatibility with Babelfish. The purpose of such analysis is to inform a Go/No Go decision about whether it makes sense to consider starting a migration project from SQL Server to Babelfish. For this purpose, Babelfish Compass produces an assessment report which lists all SQL features found in the SQL/DDL code in great detail and whether or not these are supported by the latest version of Babelfish.
docker pull mcr.microsoft.com/mssql/server:2019-latest
docker run -d -it --cap-add=SYS_PTRACE --privileged=true --name mssql2019 -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Mssql2o2o-" -e "MSSQL_PID=EnterpriseCore" -e "CONFIG_EDGE_BUILD=0" -p 1433:1433 mcr.microsoft.com/mssql/server:2019-latest
docker exec -it mssql2019 -u 0 -w /root /bin/bash
For example:
docker docker exec -it mssql2019 -u 0 -w /root /bin/bash
su - postgres
cd ~
wget https://github.com/babelfish-for-postgresql/babelfish_compass/releases/download/v.2022-12/BabelfishCompass_v.2022-12.zip
unzip BabelfishCompass_v.2022-12.zip
cd BabelfishCompass
chmod 500 BabelfishCompass.sh
If you download the Babelfish Compass source code, you need to package java to compass.jar.
root@109c95b407d8:~# apt-get install openjdk-11-jre openjdk-11-jdk
Create an SQL file that contains several table creation DDL statements for the SQL Server:
cd ~/BabelfishCompass
vi t.sql
create table a (id int, info text);
create table b (id int, info nvarchar(10));
View the instructions of using BabelfishCompass.sh commands:
postgres@109c95b407d8:~/BabelfishCompass$ ./BabelfishCompass.sh -help
Babelfish Compass v.2022-12, December 2022
Compatibility assessment tool for Babelfish for PostgreSQL
Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.
Usage: BabelfishCompass.sh <reportName> [options]
[options] can be:
inputfile [inputfile ...] : one or more input files to import into the report
-delete : first deletes report directory, incl. all report files
-appname <appname> : use application name <appname> for all inputfiles
-add : import additional inputfile(s) into existing report
-replace : replace already-imported input file(s)
-noreport : analyze only, do not generate a report
-importonly : import input file(s), no analysis or report
-reportonly : (re)generate report based on earlier analysis
-reportoption <options> : additional reporting detail (try -help -reportoption)
-reportfile <name> : specifies file name for report file (without .html)
-list : display imported files/applications for a report
-analyze : (re-)run analysis on imported files, and generate report
-userconfigfile <filename> : specifies user-defined .cfg file (default= BabelfishCompassUser.cfg)
-nooverride : do not use overrides from user-defined .cfg file
-babelfish-version <version> : specify target Babelfish version (default=latest)
-encoding <encoding> : input file encoding, e.g. '-encoding UTF16'. Default=US-ASCII
use '-encoding help' to list available encodings
-quotedid {on|off} : set QUOTED_IDENTIFIER at start of script (default=ON)
-pgimport "<comma-list>" : imports captured items into a PostgreSQL table for SQL querying
<comma-list> is: host,port,username,password,dbname
(requires psql to be installed)
-pgimportappend : with -pgimport, appends to existing table (instead of drop/recreate)
-pgimporttable <table-name> : table name for -pgimport; default=public.BBFCompass
-recursive : recursively add files if inputfile is a directory
-include <list> : pattern of input file types to include (e.g.: .txt,.ddl)
-exclude <list> : pattern of input file types to exclude (e.g.: .pptx)
-rewrite : rewrites selected unsupported SQL features
-noupdatechk : do not check for Babelfish Compass updates
-importfmt <fmt> : process special-format captured query files
-nodedup : with -importfmt, do not de-duplicate captured queries
-noreportcomplexity : do not include complexity scores in report
-syntax_issues : also report selected Babelfish syntax errors (experimental)
-version : show version of this tool
-help [ <helpoption> ] : show help information. <helpoption> can be one of:
reportoption, encoding, importfmt, exclude
-explain : some high-level migration guidance
Babelfish Compass User Guide: https://github.com/babelfish-for-postgresql/babelfish_compass/blob/main/BabelfishCompass_UserGuide.pdf
Generate a compatibility evaluation report for t.sql:
postgres@109c95b407d8:~/BabelfishCompass$ ./BabelfishCompass.sh rep -replace ${PWD}/t.sql
Babelfish Compass v.2022-12, December 2022
Compatibility assessment tool for Babelfish for PostgreSQL
Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.
Reading BabelfishFeatures.cfg
Latest Babelfish version supported: 2.3.0: BabelfishFeatures.cfg
Reading /home/postgres/BabelfishCompassReports/BabelfishCompassUser.cfg
Run starting : 18-Jan-2023 09:03:39 (Linux)
BabelfishFeatures.cfg file : v.2.3.0, Dec-2022
Target Babelfish version : v.2.3.0
Command line arguments : rep -replace
Command line input files : /home/postgres/BabelfishCompass/t.sql
User .cfg file (overrides) : /home/postgres/BabelfishCompassReports/BabelfishCompassUser.cfg
QUOTED_IDENTIFIER default : ON
Report name : rep
Report directory location : /home/postgres/BabelfishCompassReports/rep
Session log file : /home/postgres/BabelfishCompassReports/rep/log/session-log-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html
Importing /home/postgres/BabelfishCompass/t.sql, for application 't'
Replacing input file /home/postgres/BabelfishCompass/t.sql
Analyzing /home/postgres/BabelfishCompass/t.sql, for application 't'; #batches/lines: 1/2
Generating report /home/postgres/BabelfishCompassReports/rep/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html.....................
--- Run Metrics ----------------------------------------------------------------
Run start : 18-Jan-2023 09:03:39
Run end : 18-Jan-2023 09:03:40
Run time : 0 seconds
#Lines of SQL : 2 (2 lines/sec)
SQL rewrite oppties : 0
Session log : /home/postgres/BabelfishCompassReports/rep/log/session-log-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html
Assessment report : /home/postgres/BabelfishCompassReports/rep/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html
================================================================================
Report file:
/home/postgres/BabelfishCompassReports/rep/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html
Export it outside the container macOS:
docker cp mssql2019:/home/postgres/BabelfishCompassReports/rep/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html /Users/digoal/Downloads/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html
Open it with a browser, and the contents of this report are listed below:
Report for: rep : Generated at 2023-Jan-18 09:03:39
---------------------------------------------------
Babelfish Compass version 2022-12, December 2022
Compatibility assessment tool for Babelfish for PostgreSQL
Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.
Notice:
This report contains an assessment based on the resources you scanned with the
Babelfish Compass tool. The information contained in this report, including whether
or not a feature is 'supported' or 'not supported', is made available 'as is',
and may be incomplete, incorrect, and subject to interpretation.
You should not base decisions on the information in this report without independently
validating it against the actual SQL/DDL code on which this report is based.
🕮 Babelfish Compass User Guide
--- Report Setup ---------------------------------------------------------------
BabelfishFeatures.cfg file : v.2.3.0, Dec-2022
Target Babelfish version : v.2.3.0
Command line arguments : rep -replace
Command line input files : /home/postgres/BabelfishCompass/t.sql
User .cfg file (overrides) : /home/postgres/BabelfishCompassReports/BabelfishCompassUser.cfg
Report name : rep
This report : /home/postgres/BabelfishCompassReports/rep/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html
Session log : log/session-log-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html
================================================================================
--------------------------------------------------------------------------------
--- Executive Summary for Babelfish v.2.3.0 ------------------------------------
--------------------------------------------------------------------------------
Total #lines of SQL/DDL: 2
#Procedures/functions/triggers/views: 0 #Tables: 2
--------------------------------------------------------------------------------
--- Table Of Contents ----------------------------------------------------------
--------------------------------------------------------------------------------
Executive Summary
Applications Analyzed
Assessment Summary
Object Count
Summary of SQL Features 'Not Supported'
Summary of SQL Features 'Review Manually'
Summary of SQL Features 'Review Semantics'
Summary of SQL Features 'Review Performance'
Summary of SQL Features 'Ignored'
Summary of SQL Features 'Supported'
X-ref: 'Not Supported' by SQL feature
X-ref: 'Review Manually' by SQL feature
X-ref: 'Review Semantics' by SQL feature
X-ref: 'Review Performance' by SQL feature
X-ref: 'Ignored' by SQL feature
X-ref: 'Supported' by SQL feature
X-ref: 'Not Supported' by object
X-ref: 'Review Manually' by object
X-ref: 'Review Semantics' by object
X-ref: 'Review Performance' by object
X-ref: 'Ignored' by object
X-ref: 'Supported' by object
--------------------------------------------------------------------------------
--- Applications Analyzed (1) --------------------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
t (2 lines SQL)
--------------------------------------------------------------------------------
--- Assessment Summary ---------------------------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
#applications : 1
#input files : 1
#SQL batches : 1
#lines SQL/DDL processed : 2
#lines SQL in objects : 0 (procedures/functions/triggers/views)
total #SQL features : 6
Supported : 6
--------------------------------------------------------------------------------
--- Object Count ---------------------------------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
TABLE : 2 (4 columns) without issues: 2 of 2
=== SQL Features Report ========================================================
--------------------------------------------------------------------------------
--- SQL features 'Not Supported' in Babelfish v.2.3.0 --------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
-no items to report-
--------------------------------------------------------------------------------
--- SQL features 'Review Manually' in Babelfish v.2.3.0 ------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
-no items to report-
--------------------------------------------------------------------------------
--- SQL features 'Review Semantics' in Babelfish v.2.3.0 -----------------------
--------------------------------------------------------------------------------
Back to Table of Contents
-no items to report-
--------------------------------------------------------------------------------
--- SQL features 'Review Performance' in Babelfish v.2.3.0 ---------------------
--------------------------------------------------------------------------------
Back to Table of Contents
-no items to report-
--------------------------------------------------------------------------------
--- SQL features 'Ignored' in Babelfish v.2.3.0 --------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
-no items to report-
--------------------------------------------------------------------------------
--- SQL features 'Supported' in Babelfish v.2.3.0 --- (total=6) ----------------
--------------------------------------------------------------------------------
Back to Table of Contents
DDL (2/1)
CREATE TABLE : 2
Datatypes (4/3)
INT column : 2
NVARCHAR(10) column : 1
TEXT column : 1
--------------------------------------------------------------------------------
--- X-ref: 'Not Supported' by SQL feature --------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
To generate this section, specify these options with -reportoption:
'xref' or 'xref=feature'
For more options and examples, use -help -reportoption
--------------------------------------------------------------------------------
--- X-ref: 'Review Manually' by SQL feature ------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
To generate this section, specify these options with -reportoption:
'xref' or 'xref=feature'
For more options and examples, use -help -reportoption
--------------------------------------------------------------------------------
--- X-ref: 'Review Semantics' by SQL feature -----------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
To generate this section, specify these options with -reportoption:
'xref' or 'xref=feature'
For more options and examples, use -help -reportoption
--------------------------------------------------------------------------------
--- X-ref: 'Review Performance' by SQL feature ---------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
To generate this section, specify these options with -reportoption:
'xref' or 'xref=feature'
For more options and examples, use -help -reportoption
--------------------------------------------------------------------------------
--- X-ref: 'Ignored' by SQL feature --------------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
To generate this section, specify these options with -reportoption:
'xref' or 'xref=feature', and 'status=ignored' or 'status=all'
For more options and examples, use -help -reportoption
--------------------------------------------------------------------------------
--- X-ref: 'Supported' by SQL feature ------------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
To generate this section, specify these options with -reportoption:
'xref' or 'xref=feature', and 'status=supported' or 'status=all'
For more options and examples, use -help -reportoption
--------------------------------------------------------------------------------
--- X-ref: 'Not Supported' by object -------------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
To generate this section, specify these options with -reportoption:
'xref' or 'xref=object'
For more options and examples, use -help -reportoption
--------------------------------------------------------------------------------
--- X-ref: 'Review Manually' by object -----------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
To generate this section, specify these options with -reportoption:
'xref' or 'xref=object'
For more options and examples, use -help -reportoption
--------------------------------------------------------------------------------
--- X-ref: 'Review Semantics' by object ----------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
To generate this section, specify these options with -reportoption:
'xref' or 'xref=object'
For more options and examples, use -help -reportoption
--------------------------------------------------------------------------------
--- X-ref: 'Review Performance' by object --------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
To generate this section, specify these options with -reportoption:
'xref' or 'xref=object'
For more options and examples, use -help -reportoption
--------------------------------------------------------------------------------
--- X-ref: 'Ignored' by object -------------------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
To generate this section, specify these options with -reportoption:
'xref' or 'xref=object', and 'status=ignored' or 'status=all'
For more options and examples, use -help -reportoption
--------------------------------------------------------------------------------
--- X-ref: 'Supported' by object -----------------------------------------------
--------------------------------------------------------------------------------
Back to Table of Contents
To generate this section, specify these options with -reportoption:
'xref' or 'xref=object', and 'status=supported' or 'status=all'
For more options and examples, use -help -reportoption
================================================================================
--- Run Metrics ----------------------------------------------------------------
Run start : 18-Jan-2023 09:03:39
Run end : 18-Jan-2023 09:03:40
Run time : 0 seconds
#Lines of SQL : 2 (2 lines/sec)
SQL rewrite oppties : 0
Session log : /home/postgres/BabelfishCompassReports/rep/log/session-log-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html
Assessment report : /home/postgres/BabelfishCompassReports/rep/report-rep-bbf.2.3.0-2023-Jan-18-09.03.39.html
================================================================================
Configure MADlib for PolarDB to Realize the Database Machine Learning Function
digoal - July 19, 2023
ApsaraDB - June 2, 2023
Hironobu Ohara - December 1, 2022
Alibaba Cloud Community - June 2, 2023
ApsaraDB - December 25, 2023
Alibaba Cloud Community - December 2, 2022
Secure and easy solutions for moving you workloads to the cloud
Learn MoreAn on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilities
Learn MoreAn online MPP warehousing service based on the Greenplum Database open source program
Learn MoreAlibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn MoreMore Posts by digoal