Repository: reata/sqllineage
Pull request #464 (2023-10-08 15:07:05)

feat: support metadata service
provide metadata service to parse column lineage which multiple parents


---
making progress on #477 

codecov[bot]: ## [Codecov](https://app.codecov.io/gh/reata/sqllineage/pull/464?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) Report
> Merging [#464](https://app.codecov.io/gh/reata/sqllineage/pull/464?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) (f4bb89f) into [master](https://app.codecov.io/gh/reata/sqllineage/commit/a3d754b0695f6ff1512a88736bf4bc904ad24284?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) (a3d754b) will **decrease** coverage by `0.25%`.
> The diff coverage is `92.30%`.

```diff
@@            Coverage Diff             @@
##           master     #464      +/-   ##
==========================================
- Coverage   99.54%   99.30%   -0.25%     
==========================================
  Files          38       39       +1     
  Lines        1986     2005      +19     
==========================================
+ Hits         1977     1991      +14     
- Misses          9       14       +5     
```


| [Files](https://app.codecov.io/gh/reata/sqllineage/pull/464?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None) | Coverage Δ | |
|---|---|---|
| [sqllineage/runner.py](https://app.codecov.io/gh/reata/sqllineage/pull/464?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None#diff-c3FsbGluZWFnZS9ydW5uZXIucHk=) | `100.00% <100.00%> (ø)` | |
| [sqllineage/core/holders.py](https://app.codecov.io/gh/reata/sqllineage/pull/464?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None#diff-c3FsbGluZWFnZS9jb3JlL2hvbGRlcnMucHk=) | `99.46% <94.44%> (-0.54%)` | :arrow_down: |
| [sqllineage/metadata\_service.py](https://app.codecov.io/gh/reata/sqllineage/pull/464?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None#diff-c3FsbGluZWFnZS9tZXRhZGF0YV9zZXJ2aWNlLnB5) | `80.00% <80.00%> (ø)` | |

... and [2 files with indirect coverage changes](https://app.codecov.io/gh/reata/sqllineage/pull/464/indirect-changes?src=pr&el=tree-more&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=None)


lixxvsky: Change code to increase code coverage

Patch:
diff --git a/sqllineage/core/holders.py b/sqllineage/core/holders.py
index e2ec54c..35f3172 100644
--- a/sqllineage/core/holders.py
+++ b/sqllineage/core/holders.py
@@ -4,7 +4,9 @@ from typing import List, Set, Tuple, Union
 import networkx as nx
 from networkx import DiGraph
 
-from sqllineage.core.models import Column, Path, SubQuery, Table
+from sqllineage.core.metadata_provider import MetaDataProvider
+from sqllineage.core.models import Column, Path, Schema, SubQuery, Table
+from sqllineage.exceptions import InvalidSyntaxException
 from sqllineage.utils.constant import EdgeTag, EdgeType, NodeTag
 
 DATASET_CLASSES = (Path, Table)
@@ -254,7 +256,9 @@ class SQLLineageHolder(ColumnLineageMixin):
         }
 
     @staticmethod
-    def _build_digraph(*args: StatementLineageHolder) -> DiGraph:
+    def _build_digraph(
+        metadata_provider: MetaDataProvider, *args: StatementLineageHolder
+    ) -> DiGraph:
         """
         To assemble multiple :class:`sqllineage.holders.StatementLineageHolder` into
         :class:`sqllineage.holders.SQLLineageHolder`
@@ -301,11 +305,32 @@ class SQLLineageHolder(ColumnLineageMixin):
         for unresolved_col, tgt_col in unresolved_cols:
             # check if there's only one parent candidate contains the column with same name
             src_cols = []
+            # check if source column exists in graph
             for parent in unresolved_col.parent_candidates:
                 src_col = Column(unresolved_col.raw_name)
                 src_col.parent = parent
                 if g.has_edge(parent, src_col):
                     src_cols.append(src_col)
+            # if not in graph, check if defined in table schema by metadata service
+            if len(src_cols) == 0 and bool(metadata_provider):
+                for parent in unresolved_col.parent_candidates:
+                    if (
+                        isinstance(parent, Table)
+                        and str(parent.schema) != Schema.unknown
+                    ):
+                        columns = metadata_provider.get_table_columns(
+                            str(parent.schema), str(parent.raw_name)
+                        )
+                        if columns is not None and unresolved_col.raw_name in columns:
+                            src_col = Column(unresolved_col.raw_name)
+                            src_col.parent = parent
+                            src_cols.append(src_col)
+
+            if len(src_cols) > 1:
+                raise InvalidSyntaxException(
+                    f"{unresolved_col.raw_name} is not allowed from more than one table or subquery"
+                )
+
             if len(src_cols) == 1:
                 g.add_edge(src_cols[0], tgt_col, type=EdgeType.LINEAGE)
                 g.remove_edge(unresolved_col, tgt_col)
@@ -316,10 +341,10 @@ class SQLLineageHolder(ColumnLineageMixin):
         return g
 
     @staticmethod
-    def of(*args: StatementLineageHolder) -> "SQLLineageHolder":
+    def of(metadata_provider, *args: StatementLineageHolder) -> "SQLLineageHolder":
         """
         To assemble multiple :class:`sqllineage.holders.StatementLineageHolder` into
         :class:`sqllineage.holders.SQLLineageHolder`
         """
-        g = SQLLineageHolder._build_digraph(*args)
+        g = SQLLineageHolder._build_digraph(metadata_provider, *args)
         return SQLLineageHolder(g)
diff --git a/sqllineage/core/metadata/__init__.py b/sqllineage/core/metadata/__init__.py
new file mode 100644
index 0000000..e69de29
diff --git a/sqllineage/core/metadata/dummy.py b/sqllineage/core/metadata/dummy.py
new file mode 100644
index 0000000..8984486
--- /dev/null
+++ b/sqllineage/core/metadata/dummy.py
@@ -0,0 +1,18 @@
+from typing import Dict, List, Optional
+
+from sqllineage.core.metadata_provider import MetaDataProvider
+
+
+class DummyMetaDataProvider(MetaDataProvider):
+    """
+    A Dummy MetaDataProvider that accept a dict with table name as key and a set of column name as value
+    """
+
+    def __init__(self, metadata: Optional[Dict[str, List[str]]] = None):
+        self.metadata = metadata if metadata is not None else {}
+
+    def get_table_columns(self, schema: str, table: str, **kwargs) -> List[str]:
+        return self.metadata.get(f"{schema}.{table}", [])
+
+    def __bool__(self):
+        return len(self.metadata) > 0
diff --git a/sqllineage/core/metadata_provider.py b/sqllineage/core/metadata_provider.py
new file mode 100644
index 0000000..55b1284
--- /dev/null
+++ b/sqllineage/core/metadata_provider.py
@@ -0,0 +1,33 @@
+from abc import abstractmethod
+from typing import List
+
+
+class MetaDataProvider:
+    """Abstract class used to provide metadata service like table schema.
+
+    When parse below sql:
+    Insert into db1.table1
+    select c1
+    from db2.table2 t2
+    join db3.table3 t3 on t2.id = t3.id
+
+    Only by literal analysis, we don't know which table selected column c1 is from, when parsing column lineage.
+    If implement abstract method get_table_columns to provide table schema of table2 and table3,
+    then pass the implementation to :class:`sqllineage.runner.LineageRunner`.
+    It can help parse column lineage correctly.
+    """
+
+    @abstractmethod
+    def get_table_columns(self, schema: str, table: str, **kwargs) -> List[str]:
+        """Get all columns of a table.
+
+        :param schema: database name
+        :param table: table name
+        :return: a list of column names
+        """
+
+    def __bool__(self):
+        """
+        bool value tells whether this provider is ready to provide metadata
+        """
+        return True
diff --git a/sqllineage/runner.py b/sqllineage/runner.py
index c24c34b..e65bce6 100644
--- a/sqllineage/runner.py
+++ b/sqllineage/runner.py
@@ -6,6 +6,8 @@ from typing import Dict, List, Optional, Tuple
 from sqllineage import DEFAULT_DIALECT, SQLPARSE_DIALECT
 from sqllineage.config import SQLLineageConfig
 from sqllineage.core.holders import SQLLineageHolder
+from sqllineage.core.metadata.dummy import DummyMetaDataProvider
+from sqllineage.core.metadata_provider import MetaDataProvider
 from sqllineage.core.models import Column, Table
 from sqllineage.core.parser.sqlfluff.analyzer import SqlFluffLineageAnalyzer
 from sqllineage.core.parser.sqlparse.analyzer import SqlParseLineageAnalyzer
@@ -36,6 +38,7 @@ class LineageRunner(object):
         self,
         sql: str,
         dialect: str = DEFAULT_DIALECT,
+        metadata_provider: MetaDataProvider = DummyMetaDataProvider(),
         encoding: Optional[str] = None,
         verbose: bool = False,
         draw_options: Optional[Dict[str, str]] = None,
@@ -44,6 +47,8 @@ class LineageRunner(object):
         The entry point of SQLLineage after command line options are parsed.
 
         :param sql: a string representation of SQL statements.
+        :param dialect: sql dialect
+        :param metadata_provider: metadata service object providing table schema
         :param encoding: the encoding for sql string
         :param verbose: verbose flag indicate whether statement-wise lineage result will be shown
         """
@@ -62,6 +67,7 @@ class LineageRunner(object):
         self._evaluated = False
         self._stmt: List[str] = []
         self._dialect = dialect
+        self._metadata_provider = metadata_provider
 
     @lazy_method
     def __str__(self):
@@ -185,7 +191,9 @@ Target Tables:
             self._stmt = split(self._sql.strip())
 
         self._stmt_holders = [analyzer.analyze(stmt) for stmt in self._stmt]
-        self._sql_holder = SQLLineageHolder.of(*self._stmt_holders)
+        self._sql_holder = SQLLineageHolder.of(
+            self._metadata_provider, *self._stmt_holders
+        )
         self._evaluated = True
 
     @staticmethod
diff --git a/sqllineagejs/package-lock.json b/sqllineagejs/package-lock.json
index 2de8b82..3109422 100644
--- a/sqllineagejs/package-lock.json
+++ b/sqllineagejs/package-lock.json
@@ -53,12 +53,13 @@
       }
     },
     "node_modules/@babel/code-frame": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/code-frame/-/code-frame-7.22.5.tgz",
-      "integrity": "sha512-Xmwn266vad+6DAqEB2A6V/CcZVp62BbwVmcOJc2RPuwih1kw02TjQvWVWlcKGbBPd+8/0V5DEkOcizRGYsspYQ==",
+      "version": "7.22.13",
+      "resolved": "https://registry.npmjs.org/@babel/code-frame/-/code-frame-7.22.13.tgz",
+      "integrity": "sha512-XktuhWlJ5g+3TJXc5upd9Ks1HutSArik6jf2eAjYFyIOf4ej3RN+184cZbzDvbPnuTJIUhPKKJE3cIsYTiAT3w==",
       "dev": true,
       "dependencies": {
-        "@babel/highlight": "^7.22.5"
+        "@babel/highlight": "^7.22.13",
+        "chalk": "^2.4.2"
       },
       "engines": {
         "node": ">=6.9.0"
@@ -149,12 +150,12 @@
       }
     },
     "node_modules/@babel/generator": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/generator/-/generator-7.22.5.tgz",
-      "integrity": "sha512-+lcUbnTRhd0jOewtFSedLyiPsD5tswKkbgcezOqqWFUVNEwoUTlpPOBmvhG7OXWLR4jMdv0czPGH5XbflnD1EA==",
+      "version": "7.23.0",
+      "resolved": "https://registry.npmjs.org/@babel/generator/-/generator-7.23.0.tgz",
+      "integrity": "sha512-lN85QRR+5IbYrMWM6Y4pE/noaQtg4pNiqeNGX60eqOfo6gtEj6uw/JagelB8vVztSd7R6M5n1+PQkDbHbBRU4g==",
       "dev": true,
       "dependencies": {
-        "@babel/types": "^7.22.5",
+        "@babel/types": "^7.23.0",
         "@jridgewell/gen-mapping": "^0.3.2",
         "@jridgewell/trace-mapping": "^0.3.17",
         "jsesc": "^2.5.1"
@@ -315,22 +316,22 @@
       }
     },
     "node_modules/@babel/helper-environment-visitor": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/helper-environment-visitor/-/helper-environment-visitor-7.22.5.tgz",
-      "integrity": "sha512-XGmhECfVA/5sAt+H+xpSg0mfrHq6FzNr9Oxh7PSEBBRUb/mL7Kz3NICXb194rCqAEdxkhPT1a88teizAFyvk8Q==",
+      "version": "7.22.20",
+      "resolved": "https://registry.npmjs.org/@babel/helper-environment-visitor/-/helper-environment-visitor-7.22.20.tgz",
+      "integrity": "sha512-zfedSIzFhat/gFhWfHtgWvlec0nqB9YEIVrpuwjruLlXfUSnA8cJB0miHKwqDnQ7d32aKo2xt88/xZptwxbfhA==",
       "dev": true,
       "engines": {
         "node": ">=6.9.0"
       }
     },
     "node_modules/@babel/helper-function-name": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/helper-function-name/-/helper-function-name-7.22.5.tgz",
-      "integrity": "sha512-wtHSq6jMRE3uF2otvfuD3DIvVhOsSNshQl0Qrd7qC9oQJzHvOL4qQXlQn2916+CXGywIjpGuIkoyZRRxHPiNQQ==",
+      "version": "7.23.0",
+      "resolved": "https://registry.npmjs.org/@babel/helper-function-name/-/helper-function-name-7.23.0.tgz",
+      "integrity": "sha512-OErEqsrxjZTJciZ4Oo+eoZqeW9UIiOcuYKRJA4ZAgV9myA+pOXhhmpfNCKjEH/auVfEYVFJ6y1Tc4r0eIApqiw==",
       "dev": true,
       "dependencies": {
-        "@babel/template": "^7.22.5",
-        "@babel/types": "^7.22.5"
+        "@babel/template": "^7.22.15",
+        "@babel/types": "^7.23.0"
       },
       "engines": {
         "node": ">=6.9.0"
@@ -472,9 +473,9 @@
       }
     },
     "node_modules/@babel/helper-split-export-declaration": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/helper-split-export-declaration/-/helper-split-export-declaration-7.22.5.tgz",
-      "integrity": "sha512-thqK5QFghPKWLhAV321lxF95yCg2K3Ob5yw+M3VHWfdia0IkPXUtoLH8x/6Fh486QUvzhb8YOWHChTVen2/PoQ==",
+      "version": "7.22.6",
+      "resolved": "https://registry.npmjs.org/@babel/helper-split-export-declaration/-/helper-split-export-declaration-7.22.6.tgz",
+      "integrity": "sha512-AsUnxuLhRYsisFiaJwvp1QF+I3KjD5FOxut14q/GzovUe6orHLesW2C7d754kRm53h5gqrz6sFl6sxc4BVtE/g==",
       "dev": true,
       "dependencies": {
         "@babel/types": "^7.22.5"
@@ -493,9 +494,9 @@
       }
     },
     "node_modules/@babel/helper-validator-identifier": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/helper-validator-identifier/-/helper-validator-identifier-7.22.5.tgz",
-      "integrity": "sha512-aJXu+6lErq8ltp+JhkJUfk1MTGyuA4v7f3pA+BJ5HLfNC6nAQ0Cpi9uOquUj8Hehg0aUiHzWQbOVJGao6ztBAQ==",
+      "version": "7.22.20",
+      "resolved": "https://registry.npmjs.org/@babel/helper-validator-identifier/-/helper-validator-identifier-7.22.20.tgz",
+      "integrity": "sha512-Y4OZ+ytlatR8AI+8KZfKuL5urKp7qey08ha31L8b3BwewJAoJamTzyvxPR/5D+KkdJCGPq/+8TukHBlY10FX9A==",
       "dev": true,
       "engines": {
         "node": ">=6.9.0"
@@ -540,13 +541,13 @@
       }
     },
     "node_modules/@babel/highlight": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/highlight/-/highlight-7.22.5.tgz",
-      "integrity": "sha512-BSKlD1hgnedS5XRnGOljZawtag7H1yPfQp0tdNJCHoH6AZ+Pcm9VvkrK59/Yy593Ypg0zMxH2BxD1VPYUQ7UIw==",
+      "version": "7.22.20",
+      "resolved": "https://registry.npmjs.org/@babel/highlight/-/highlight-7.22.20.tgz",
+      "integrity": "sha512-dkdMCN3py0+ksCgYmGG8jKeGA/8Tk+gJwSYYlFGxG5lmhfKNoAy004YpLxpS1W2J8m/EK2Ew+yOs9pVRwO89mg==",
       "dev": true,
       "dependencies": {
-        "@babel/helper-validator-identifier": "^7.22.5",
-        "chalk": "^2.0.0",
+        "@babel/helper-validator-identifier": "^7.22.20",
+        "chalk": "^2.4.2",
         "js-tokens": "^4.0.0"
       },
       "engines": {
@@ -554,9 +555,9 @@
       }
     },
     "node_modules/@babel/parser": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/parser/-/parser-7.22.5.tgz",
-      "integrity": "sha512-DFZMC9LJUG9PLOclRC32G63UXwzqS2koQC8dkx+PLdmt1xSePYpbT/NbsrJy8Q/muXz7o/h/d4A7Fuyixm559Q==",
+      "version": "7.23.0",
+      "resolved": "https://registry.npmjs.org/@babel/parser/-/parser-7.23.0.tgz",
+      "integrity": "sha512-vvPKKdMemU85V9WE/l5wZEmImpCtLqbnTvqDS2U1fJ96KrxoW7KrXhNsNCblQlg8Ck4b85yxdTyelsMUgFUXiw==",
       "dev": true,
       "bin": {
         "parser": "bin/babel-parser.js"
@@ -2151,33 +2152,33 @@
       }
     },
     "node_modules/@babel/template": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/template/-/template-7.22.5.tgz",
-      "integrity": "sha512-X7yV7eiwAxdj9k94NEylvbVHLiVG1nvzCV2EAowhxLTwODV1jl9UzZ48leOC0sH7OnuHrIkllaBgneUykIcZaw==",
+      "version": "7.22.15",
+      "resolved": "https://registry.npmjs.org/@babel/template/-/template-7.22.15.tgz",
+      "integrity": "sha512-QPErUVm4uyJa60rkI73qneDacvdvzxshT3kksGqlGWYdOTIUOwJ7RDUL8sGqslY1uXWSL6xMFKEXDS3ox2uF0w==",
       "dev": true,
       "dependencies": {
-        "@babel/code-frame": "^7.22.5",
-        "@babel/parser": "^7.22.5",
-        "@babel/types": "^7.22.5"
+        "@babel/code-frame": "^7.22.13",
+        "@babel/parser": "^7.22.15",
+        "@babel/types": "^7.22.15"
       },
       "engines": {
         "node": ">=6.9.0"
       }
     },
     "node_modules/@babel/traverse": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/traverse/-/traverse-7.22.5.tgz",
-      "integrity": "sha512-7DuIjPgERaNo6r+PZwItpjCZEa5vyw4eJGufeLxrPdBXBoLcCJCIasvK6pK/9DVNrLZTLFhUGqaC6X/PA007TQ==",
+      "version": "7.23.2",
+      "resolved": "https://registry.npmjs.org/@babel/traverse/-/traverse-7.23.2.tgz",
+      "integrity": "sha512-azpe59SQ48qG6nu2CzcMLbxUudtN+dOM9kDbUqGq3HXUJRlo7i8fvPoxQUzYgLZ4cMVmuZgm8vvBpNeRhd6XSw==",
       "dev": true,
       "dependencies": {
-        "@babel/code-frame": "^7.22.5",
-        "@babel/generator": "^7.22.5",
-        "@babel/helper-environment-visitor": "^7.22.5",
-        "@babel/helper-function-name": "^7.22.5",
+        "@babel/code-frame": "^7.22.13",
+        "@babel/generator": "^7.23.0",
+        "@babel/helper-environment-visitor": "^7.22.20",
+        "@babel/helper-function-name": "^7.23.0",
         "@babel/helper-hoist-variables": "^7.22.5",
-        "@babel/helper-split-export-declaration": "^7.22.5",
-        "@babel/parser": "^7.22.5",
-        "@babel/types": "^7.22.5",
+        "@babel/helper-split-export-declaration": "^7.22.6",
+        "@babel/parser": "^7.23.0",
+        "@babel/types": "^7.23.0",
         "debug": "^4.1.0",
         "globals": "^11.1.0"
       },
@@ -2186,13 +2187,13 @@
       }
     },
     "node_modules/@babel/types": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/types/-/types-7.22.5.tgz",
-      "integrity": "sha512-zo3MIHGOkPOfoRXitsgHLjEXmlDaD/5KU1Uzuc9GNiZPhSqVxVRtxuPaSBZDsYZ9qV88AjtMtWW7ww98loJ9KA==",
+      "version": "7.23.0",
+      "resolved": "https://registry.npmjs.org/@babel/types/-/types-7.23.0.tgz",
+      "integrity": "sha512-0oIyUfKoI3mSqMvsxBdclDwxXKXAUA8v/apZbc+iSyARYou1o8ZGDxbUYyLFoW2arqS2jDGqJuZvv1d/io1axg==",
       "dev": true,
       "dependencies": {
         "@babel/helper-string-parser": "^7.22.5",
-        "@babel/helper-validator-identifier": "^7.22.5",
+        "@babel/helper-validator-identifier": "^7.22.20",
         "to-fast-properties": "^2.0.0"
       },
       "engines": {
@@ -18943,12 +18944,13 @@
       }
     },
     "@babel/code-frame": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/code-frame/-/code-frame-7.22.5.tgz",
-      "integrity": "sha512-Xmwn266vad+6DAqEB2A6V/CcZVp62BbwVmcOJc2RPuwih1kw02TjQvWVWlcKGbBPd+8/0V5DEkOcizRGYsspYQ==",
+      "version": "7.22.13",
+      "resolved": "https://registry.npmjs.org/@babel/code-frame/-/code-frame-7.22.13.tgz",
+      "integrity": "sha512-XktuhWlJ5g+3TJXc5upd9Ks1HutSArik6jf2eAjYFyIOf4ej3RN+184cZbzDvbPnuTJIUhPKKJE3cIsYTiAT3w==",
       "dev": true,
       "requires": {
-        "@babel/highlight": "^7.22.5"
+        "@babel/highlight": "^7.22.13",
+        "chalk": "^2.4.2"
       }
     },
     "@babel/compat-data": {
@@ -19014,12 +19016,12 @@
       }
     },
     "@babel/generator": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/generator/-/generator-7.22.5.tgz",
-      "integrity": "sha512-+lcUbnTRhd0jOewtFSedLyiPsD5tswKkbgcezOqqWFUVNEwoUTlpPOBmvhG7OXWLR4jMdv0czPGH5XbflnD1EA==",
+      "version": "7.23.0",
+      "resolved": "https://registry.npmjs.org/@babel/generator/-/generator-7.23.0.tgz",
+      "integrity": "sha512-lN85QRR+5IbYrMWM6Y4pE/noaQtg4pNiqeNGX60eqOfo6gtEj6uw/JagelB8vVztSd7R6M5n1+PQkDbHbBRU4g==",
       "dev": true,
       "requires": {
-        "@babel/types": "^7.22.5",
+        "@babel/types": "^7.23.0",
         "@jridgewell/gen-mapping": "^0.3.2",
         "@jridgewell/trace-mapping": "^0.3.17",
         "jsesc": "^2.5.1"
@@ -19146,19 +19148,19 @@
       }
     },
     "@babel/helper-environment-visitor": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/helper-environment-visitor/-/helper-environment-visitor-7.22.5.tgz",
-      "integrity": "sha512-XGmhECfVA/5sAt+H+xpSg0mfrHq6FzNr9Oxh7PSEBBRUb/mL7Kz3NICXb194rCqAEdxkhPT1a88teizAFyvk8Q==",
+      "version": "7.22.20",
+      "resolved": "https://registry.npmjs.org/@babel/helper-environment-visitor/-/helper-environment-visitor-7.22.20.tgz",
+      "integrity": "sha512-zfedSIzFhat/gFhWfHtgWvlec0nqB9YEIVrpuwjruLlXfUSnA8cJB0miHKwqDnQ7d32aKo2xt88/xZptwxbfhA==",
       "dev": true
     },
     "@babel/helper-function-name": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/helper-function-name/-/helper-function-name-7.22.5.tgz",
-      "integrity": "sha512-wtHSq6jMRE3uF2otvfuD3DIvVhOsSNshQl0Qrd7qC9oQJzHvOL4qQXlQn2916+CXGywIjpGuIkoyZRRxHPiNQQ==",
+      "version": "7.23.0",
+      "resolved": "https://registry.npmjs.org/@babel/helper-function-name/-/helper-function-name-7.23.0.tgz",
+      "integrity": "sha512-OErEqsrxjZTJciZ4Oo+eoZqeW9UIiOcuYKRJA4ZAgV9myA+pOXhhmpfNCKjEH/auVfEYVFJ6y1Tc4r0eIApqiw==",
       "dev": true,
       "requires": {
-        "@babel/template": "^7.22.5",
-        "@babel/types": "^7.22.5"
+        "@babel/template": "^7.22.15",
+        "@babel/types": "^7.23.0"
       }
     },
     "@babel/helper-hoist-variables": {
@@ -19264,9 +19266,9 @@
       }
     },
     "@babel/helper-split-export-declaration": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/helper-split-export-declaration/-/helper-split-export-declaration-7.22.5.tgz",
-      "integrity": "sha512-thqK5QFghPKWLhAV321lxF95yCg2K3Ob5yw+M3VHWfdia0IkPXUtoLH8x/6Fh486QUvzhb8YOWHChTVen2/PoQ==",
+      "version": "7.22.6",
+      "resolved": "https://registry.npmjs.org/@babel/helper-split-export-declaration/-/helper-split-export-declaration-7.22.6.tgz",
+      "integrity": "sha512-AsUnxuLhRYsisFiaJwvp1QF+I3KjD5FOxut14q/GzovUe6orHLesW2C7d754kRm53h5gqrz6sFl6sxc4BVtE/g==",
       "dev": true,
       "requires": {
         "@babel/types": "^7.22.5"
@@ -19279,9 +19281,9 @@
       "dev": true
     },
     "@babel/helper-validator-identifier": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/helper-validator-identifier/-/helper-validator-identifier-7.22.5.tgz",
-      "integrity": "sha512-aJXu+6lErq8ltp+JhkJUfk1MTGyuA4v7f3pA+BJ5HLfNC6nAQ0Cpi9uOquUj8Hehg0aUiHzWQbOVJGao6ztBAQ==",
+      "version": "7.22.20",
+      "resolved": "https://registry.npmjs.org/@babel/helper-validator-identifier/-/helper-validator-identifier-7.22.20.tgz",
+      "integrity": "sha512-Y4OZ+ytlatR8AI+8KZfKuL5urKp7qey08ha31L8b3BwewJAoJamTzyvxPR/5D+KkdJCGPq/+8TukHBlY10FX9A==",
       "dev": true
     },
     "@babel/helper-validator-option": {
@@ -19314,20 +19316,20 @@
       }
     },
     "@babel/highlight": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/highlight/-/highlight-7.22.5.tgz",
-      "integrity": "sha512-BSKlD1hgnedS5XRnGOljZawtag7H1yPfQp0tdNJCHoH6AZ+Pcm9VvkrK59/Yy593Ypg0zMxH2BxD1VPYUQ7UIw==",
+      "version": "7.22.20",
+      "resolved": "https://registry.npmjs.org/@babel/highlight/-/highlight-7.22.20.tgz",
+      "integrity": "sha512-dkdMCN3py0+ksCgYmGG8jKeGA/8Tk+gJwSYYlFGxG5lmhfKNoAy004YpLxpS1W2J8m/EK2Ew+yOs9pVRwO89mg==",
       "dev": true,
       "requires": {
-        "@babel/helper-validator-identifier": "^7.22.5",
-        "chalk": "^2.0.0",
+        "@babel/helper-validator-identifier": "^7.22.20",
+        "chalk": "^2.4.2",
         "js-tokens": "^4.0.0"
       }
     },
     "@babel/parser": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/parser/-/parser-7.22.5.tgz",
-      "integrity": "sha512-DFZMC9LJUG9PLOclRC32G63UXwzqS2koQC8dkx+PLdmt1xSePYpbT/NbsrJy8Q/muXz7o/h/d4A7Fuyixm559Q==",
+      "version": "7.23.0",
+      "resolved": "https://registry.npmjs.org/@babel/parser/-/parser-7.23.0.tgz",
+      "integrity": "sha512-vvPKKdMemU85V9WE/l5wZEmImpCtLqbnTvqDS2U1fJ96KrxoW7KrXhNsNCblQlg8Ck4b85yxdTyelsMUgFUXiw==",
       "dev": true
     },
     "@babel/plugin-bugfix-safari-id-destructuring-collision-in-function-expression": {
@@ -20390,42 +20392,42 @@
       }
     },
     "@babel/template": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/template/-/template-7.22.5.tgz",
-      "integrity": "sha512-X7yV7eiwAxdj9k94NEylvbVHLiVG1nvzCV2EAowhxLTwODV1jl9UzZ48leOC0sH7OnuHrIkllaBgneUykIcZaw==",
+      "version": "7.22.15",
+      "resolved": "https://registry.npmjs.org/@babel/template/-/template-7.22.15.tgz",
+      "integrity": "sha512-QPErUVm4uyJa60rkI73qneDacvdvzxshT3kksGqlGWYdOTIUOwJ7RDUL8sGqslY1uXWSL6xMFKEXDS3ox2uF0w==",
       "dev": true,
       "requires": {
-        "@babel/code-frame": "^7.22.5",
-        "@babel/parser": "^7.22.5",
-        "@babel/types": "^7.22.5"
+        "@babel/code-frame": "^7.22.13",
+        "@babel/parser": "^7.22.15",
+        "@babel/types": "^7.22.15"
       }
     },
     "@babel/traverse": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/traverse/-/traverse-7.22.5.tgz",
-      "integrity": "sha512-7DuIjPgERaNo6r+PZwItpjCZEa5vyw4eJGufeLxrPdBXBoLcCJCIasvK6pK/9DVNrLZTLFhUGqaC6X/PA007TQ==",
+      "version": "7.23.2",
+      "resolved": "https://registry.npmjs.org/@babel/traverse/-/traverse-7.23.2.tgz",
+      "integrity": "sha512-azpe59SQ48qG6nu2CzcMLbxUudtN+dOM9kDbUqGq3HXUJRlo7i8fvPoxQUzYgLZ4cMVmuZgm8vvBpNeRhd6XSw==",
       "dev": true,
       "requires": {
-        "@babel/code-frame": "^7.22.5",
-        "@babel/generator": "^7.22.5",
-        "@babel/helper-environment-visitor": "^7.22.5",
-        "@babel/helper-function-name": "^7.22.5",
+        "@babel/code-frame": "^7.22.13",
+        "@babel/generator": "^7.23.0",
+        "@babel/helper-environment-visitor": "^7.22.20",
+        "@babel/helper-function-name": "^7.23.0",
         "@babel/helper-hoist-variables": "^7.22.5",
-        "@babel/helper-split-export-declaration": "^7.22.5",
-        "@babel/parser": "^7.22.5",
-        "@babel/types": "^7.22.5",
+        "@babel/helper-split-export-declaration": "^7.22.6",
+        "@babel/parser": "^7.23.0",
+        "@babel/types": "^7.23.0",
         "debug": "^4.1.0",
         "globals": "^11.1.0"
       }
     },
     "@babel/types": {
-      "version": "7.22.5",
-      "resolved": "https://registry.npmjs.org/@babel/types/-/types-7.22.5.tgz",
-      "integrity": "sha512-zo3MIHGOkPOfoRXitsgHLjEXmlDaD/5KU1Uzuc9GNiZPhSqVxVRtxuPaSBZDsYZ9qV88AjtMtWW7ww98loJ9KA==",
+      "version": "7.23.0",
+      "resolved": "https://registry.npmjs.org/@babel/types/-/types-7.23.0.tgz",
+      "integrity": "sha512-0oIyUfKoI3mSqMvsxBdclDwxXKXAUA8v/apZbc+iSyARYou1o8ZGDxbUYyLFoW2arqS2jDGqJuZvv1d/io1axg==",
       "dev": true,
       "requires": {
         "@babel/helper-string-parser": "^7.22.5",
-        "@babel/helper-validator-identifier": "^7.22.5",
+        "@babel/helper-validator-identifier": "^7.22.20",
         "to-fast-properties": "^2.0.0"
       }
     },


Test patch:
diff --git a/tests/helpers.py b/tests/helpers.py
index cf22424..cd756b4 100644
--- a/tests/helpers.py
+++ b/tests/helpers.py
@@ -1,6 +1,10 @@
+from typing import Optional
+
 import networkx as nx
 
 from sqllineage import SQLPARSE_DIALECT
+from sqllineage.core.metadata.dummy import DummyMetaDataProvider
+from sqllineage.core.metadata_provider import MetaDataProvider
 from sqllineage.core.models import Column, Table
 from sqllineage.runner import LineageRunner
 
@@ -62,12 +66,20 @@ def assert_column_lineage_equal(
     sql: str,
     column_lineages=None,
     dialect: str = "ansi",
+    metadata_provider: Optional[MetaDataProvider] = None,
     test_sqlfluff: bool = True,
     test_sqlparse: bool = True,
     skip_graph_check: bool = False,
 ):
-    lr = LineageRunner(sql, dialect=SQLPARSE_DIALECT)
-    lr_sqlfluff = LineageRunner(sql, dialect=dialect)
+    metadata_provider = (
+        DummyMetaDataProvider() if metadata_provider is None else metadata_provider
+    )
+    lr = LineageRunner(
+        sql, dialect=SQLPARSE_DIALECT, metadata_provider=metadata_provider
+    )
+    lr_sqlfluff = LineageRunner(
+        sql, dialect=dialect, metadata_provider=metadata_provider
+    )
     if test_sqlparse:
         assert_column_lineage(lr, column_lineages)
     if test_sqlfluff:
diff --git a/tests/sql/column/test_column_metadata.py b/tests/sql/column/test_column_metadata.py
new file mode 100644
index 0000000..f48059d
--- /dev/null
+++ b/tests/sql/column/test_column_metadata.py
@@ -0,0 +1,296 @@
+import pytest
+from tests.helpers import assert_column_lineage_equal
+
+from sqllineage.core.metadata.dummy import DummyMetaDataProvider
+from sqllineage.exceptions import InvalidSyntaxException
+from sqllineage.runner import LineageRunner
+from sqllineage.utils.entities import ColumnQualifierTuple
+
+
+test_schemas = {
+    "db1.table1": ["id", "a", "b", "c", "d"],
+    "db2.table2": ["id", "h", "i", "j", "k"],
+    "db3.table3": ["pk", "p", "q", "r"],
+}
+
+
+def test_select_column_from_tables():
+    provider = DummyMetaDataProvider(test_schemas)
+
+    sql = """insert into db.tbl
+select t1.id, a as x, b, h, i as y
+from db1.table1 t1
+join db2.table2 t2 on t1.id = t2.id
+"""
+    assert_column_lineage_equal(
+        sql,
+        [
+            (
+                ColumnQualifierTuple("id", "db1.table1"),
+                ColumnQualifierTuple("id", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("a", "db1.table1"),
+                ColumnQualifierTuple("x", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("b", "db1.table1"),
+                ColumnQualifierTuple("b", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("h", "db2.table2"),
+                ColumnQualifierTuple("h", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("i", "db2.table2"),
+                ColumnQualifierTuple("y", "db.tbl"),
+            ),
+        ],
+        metadata_provider=provider,
+    )
+
+    sql = """insert into db.tbl
+    select a, b as x, h, i as y, p, q, pk as z
+    from db1.table1 t1
+    left join db2.table2 t2 on t1.id = t2.id
+    right join db3.table3 t3 on t2.id = t3.pk
+    """
+    assert_column_lineage_equal(
+        sql,
+        [
+            (
+                ColumnQualifierTuple("a", "db1.table1"),
+                ColumnQualifierTuple("a", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("b", "db1.table1"),
+                ColumnQualifierTuple("x", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("h", "db2.table2"),
+                ColumnQualifierTuple("h", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("i", "db2.table2"),
+                ColumnQualifierTuple("y", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("p", "db3.table3"),
+                ColumnQualifierTuple("p", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("q", "db3.table3"),
+                ColumnQualifierTuple("q", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("pk", "db3.table3"),
+                ColumnQualifierTuple("z", "db.tbl"),
+            ),
+        ],
+        metadata_provider=provider,
+    )
+
+
+def test_select_column_from_subqueries():
+    provider = DummyMetaDataProvider(test_schemas)
+
+    sql = """insert into db.tbl
+select a, b as x, h, y
+from (select a, b, c from db1.table1) t1
+full join (select h, i, j as y from db2.table2) t2
+on t1.id = t2.id
+"""
+    assert_column_lineage_equal(
+        sql,
+        [
+            (
+                ColumnQualifierTuple("a", "db1.table1"),
+                ColumnQualifierTuple("a", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("b", "db1.table1"),
+                ColumnQualifierTuple("x", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("h", "db2.table2"),
+                ColumnQualifierTuple("h", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("j", "db2.table2"),
+                ColumnQualifierTuple("y", "db.tbl"),
+            ),
+        ],
+        metadata_provider=provider,
+    )
+
+    sql = """insert into db.tbl
+select a, b as x, h, y, pk as z, p
+from (select id, a, b, c from db1.table1) t1
+join (select id, h, i as y, j from db2.table2) t2 on t1.id = t2.id
+left join (select pk, p, q from db3.table3) t3 on t1.id = t3.pk
+"""
+    assert_column_lineage_equal(
+        sql,
+        [
+            (
+                ColumnQualifierTuple("a", "db1.table1"),
+                ColumnQualifierTuple("a", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("b", "db1.table1"),
+                ColumnQualifierTuple("x", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("h", "db2.table2"),
+                ColumnQualifierTuple("h", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("i", "db2.table2"),
+                ColumnQualifierTuple("y", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("p", "db3.table3"),
+                ColumnQualifierTuple("p", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("pk", "db3.table3"),
+                ColumnQualifierTuple("z", "db.tbl"),
+            ),
+        ],
+        metadata_provider=provider,
+    )
+
+
+def test_select_column_from_table_subquery():
+    provider = DummyMetaDataProvider(test_schemas)
+
+    sql = """insert into db.tbl
+select a as x, b, q, pk as y
+from db1.table1 t1
+right join (select pk, p, q from db3.table3) t3
+on t1.id = t3.pk
+"""
+    assert_column_lineage_equal(
+        sql,
+        [
+            (
+                ColumnQualifierTuple("a", "db1.table1"),
+                ColumnQualifierTuple("x", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("b", "db1.table1"),
+                ColumnQualifierTuple("b", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("pk", "db3.table3"),
+                ColumnQualifierTuple("y", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("q", "db3.table3"),
+                ColumnQualifierTuple("q", "db.tbl"),
+            ),
+        ],
+        metadata_provider=provider,
+    )
+
+    sql = """insert into db.tbl
+select a, x, h as y, i, p, z
+from (select id, a, b as x, c from db1.table1) t1
+join db2.table2 t2 on t1.id = t2.id
+left join (select pk, p, q as z from db3.table3) t3 on t2.id = t3.pk
+"""
+    assert_column_lineage_equal(
+        sql,
+        [
+            (
+                ColumnQualifierTuple("a", "db1.table1"),
+                ColumnQualifierTuple("a", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("b", "db1.table1"),
+                ColumnQualifierTuple("x", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("h", "db2.table2"),
+                ColumnQualifierTuple("y", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("i", "db2.table2"),
+                ColumnQualifierTuple("i", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("p", "db3.table3"),
+                ColumnQualifierTuple("p", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("q", "db3.table3"),
+                ColumnQualifierTuple("z", "db.tbl"),
+            ),
+        ],
+        metadata_provider=provider,
+    )
+
+
+def test_select_column_from_tempview_view_subquery():
+    provider = DummyMetaDataProvider(test_schemas)
+
+    sql = """
+create or replace view test_view
+as
+select id, a from db1.table1
+;
+
+insert into db.tbl
+select test_view.id, a as x, h as y, i, p, z
+from test_view
+join db2.table2 t2 on test_view.id = t2.id
+left join (select pk, p, q as z from db3.table3) t3 on t2.id = t3.pk
+"""
+    assert_column_lineage_equal(
+        sql,
+        [
+            (
+                ColumnQualifierTuple("id", "db1.table1"),
+                ColumnQualifierTuple("id", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("a", "db1.table1"),
+                ColumnQualifierTuple("x", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("h", "db2.table2"),
+                ColumnQualifierTuple("y", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("i", "db2.table2"),
+                ColumnQualifierTuple("i", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("p", "db3.table3"),
+                ColumnQualifierTuple("p", "db.tbl"),
+            ),
+            (
+                ColumnQualifierTuple("q", "db3.table3"),
+                ColumnQualifierTuple("z", "db.tbl"),
+            ),
+        ],
+        metadata_provider=provider,
+    )
+
+
+def test_sqlparse_exception():
+    provider = DummyMetaDataProvider(test_schemas)
+    sql = """insert into table db.tbl
+select id
+from db1.table1 t1
+join db2.table2 t2 on t1.id = t2.id
+"""
+
+    with pytest.raises(
+        InvalidSyntaxException,
+        match="id is not allowed from more than one table or subquery",
+    ):
+        lr = LineageRunner(sql, metadata_provider=provider)
+        col_lineage = lr.get_column_lineage()
+        for e in col_lineage:
+            print(e)