To extract specific pattern from file

Question

I am having below file file.txt where i need to extract pattern.

CREATE TABLE `test`(
  `id` string COMMENT '',
  `age` string COMMENT '',
  `city` string COMMENT '')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
WITH SERDEPROPERTIES (
  'path'='hdfs://local/')
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://local/'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false',
  'EXTERNAL'='FALSE',
  'numFiles'='14',
  'numRows'='-1',
  'rawDataSize'='-1',
  'spark.sql.sources.provider'='orc',
  'spark.sql.sources.schema.numParts'='9',
'spark.sql.sources.schema.part.8'='....":{}}]}',
  'totalSize'='12',
  'transient_lastDdlTime'='12')

I would require below

CREATE TABLE `test`(
  `id` string COMMENT '',
  `age` string COMMENT '',
  `city` string COMMENT '')

How is it possible to achieve in Unix ?

Answer 1

If you know number of columns in table, you can use grep. In this case with 3 columns use this:

grep -A 3 'CREATE TABLE' file.txt

Where 3 after -A is number of lines after match which will be included to output.

If you do not know number of columns use awk.