Maven依赖
源头
<dependencies>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-table-planner_2.11</artifactId>
<version>1.8.0</version>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-table-api-java-bridge_2.11</artifactId>
<version>1.8.0</version>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-streaming-scala_2.11</artifactId>
<version>1.8.0</version>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-table-common</artifactId>
<version>1.8.0</version>
</dependency>
</dependencies>
改版
<dependencies>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-table_2.11</artifactId>
<version>1.7.2</version>
</dependency>
<dependency>
<groupId>org.apache.flink</groupId>
<artifactId>flink-streaming-scala_2.11</artifactId>
<version>1.8.0</version>
</dependency>
</dependencies>
SQL语句
SELECT COUNT(*) FROM T13_REF_AIRPORT_SAT;--11008
--HUB_ID IATA_CD NAME_CN NAME_EN
SELECT COUNT(*) FROM T13_REF_AIRPORT_CITY_LINK;--9676
--*******LINK_ID AIRPORT_HUB_ID CITY_HUB_ID
SELECT COUNT(*) FROM T13_REF_CITY_SAT;--9624
--HUB_ID CITY_CD NAME_CN NAME_EN
SELECT COUNT(*) FROM T13_REF_CITY_COUNTRY_LINK;--9062
--*******LINK_ID COUNTRY_HUB_ID CITY_HUB_ID
SELECT COUNT(*) FROM T13_REF_COUNTRY_SAT;--356
--HUB_ID COUNTRY_CD NAME_CN NAME_EN
SELECT *
FROM T13_REF_AIRPORT_SAT X1,T13_REF_AIRPORT_CITY_LINK X2,T13_REF_CITY_SAT X3,T13_REF_CITY_COUNTRY_LINK X4,T13_REF_COUNTRY_SAT X5
WHERE X1.HUB_ID=X2.AIRPORT_HUB_ID
AND X2.CITY_HUB_ID=X3.HUB_ID
AND X3.HUB_ID=X4.CITY_HUB_ID
AND X4.COUNTRY_HUB_ID=X5.HUB_ID;
SELECT COUNT(*)
FROM T13_REF_AIRPORT_SAT X1,T13_REF_AIRPORT_CITY_LINK X2,T13_REF_CITY_SAT X3,T13_REF_CITY_COUNTRY_LINK X4,T13_REF_COUNTRY_SAT X5
WHERE X1.HUB_ID=X2.AIRPORT_HUB_ID
AND X2.CITY_HUB_ID=X3.HUB_ID
AND X3.HUB_ID=X4.CITY_HUB_ID
AND X4.COUNTRY_HUB_ID=X5.HUB_ID;--16759
SELECT X5.NAME_CN COUNTRY_CN_NAME,COUNT(X1.HUB_ID) COUNT_AIRPORT
FROM T13_REF_AIRPORT_SAT X1,T13_REF_AIRPORT_CITY_LINK X2,T13_REF_CITY_SAT X3,T13_REF_CITY_COUNTRY_LINK X4,T13_REF_COUNTRY_SAT X5
WHERE X1.HUB_ID=X2.AIRPORT_HUB_ID
AND X2.CITY_HUB_ID=X3.HUB_ID
AND X3.HUB_ID=X4.CITY_HUB_ID
AND X4.COUNTRY_HUB_ID=X5.HUB_ID
GROUP BY X5.NAME_CN
ORDER BY COUNT_AIRPORT DESC;--254
SELECT
X5.COUNTRY_CD,
X5.NAME_CN COUNTRY_NAME_CN,
X5.NAME_EN COUNTRY_NAME_EN,
X3.CITY_CD,
X3.NAME_CN CITY_CN_NAME,
X3.NAME_EN CITY_EN_NAME,
COUNT(X1.HUB_ID) COUNT_AIRPORT
FROM T13_REF_AIRPORT_SAT X1,T13_REF_AIRPORT_CITY_LINK X2,T13_REF_CITY_SAT X3,T13_REF_CITY_COUNTRY_LINK X4,T13_REF_COUNTRY_SAT X5
WHERE X1.HUB_ID=X2.AIRPORT_HUB_ID
AND X2.CITY_HUB_ID=X3.HUB_ID
AND X3.HUB_ID=X4.CITY_HUB_ID
AND X4.COUNTRY_HUB_ID=X5.HUB_ID
GROUP BY X5.COUNTRY_CD,X5.NAME_CN,X5.NAME_EN,X3.CITY_CD,X3.NAME_CN,X3.NAME_EN
ORDER BY COUNT_AIRPORT DESC;--13030
SELECT
X5.COUNTRY_CD,
X5.NAME_CN COUNTRY_NAME_CN,
X5.NAME_EN COUNTRY_NAME_EN,
X3.CITY_CD,
X3.NAME_CN CITY_CN_NAME,
X3.NAME_EN CITY_EN_NAME,
COUNT(X1.HUB_ID) COUNT_AIRPORT
FROM T13_REF_AIRPORT_SAT X1,T13_REF_AIRPORT_CITY_LINK X2,T13_REF_CITY_SAT X3,T13_REF_CITY_COUNTRY_LINK X4,T13_REF_COUNTRY_SAT X5
WHERE X1.HUB_ID=X2.AIRPORT_HUB_ID
AND X2.CITY_HUB_ID=X3.HUB_ID
AND X3.HUB_ID=X4.CITY_HUB_ID
AND X4.COUNTRY_HUB_ID=X5.HUB_ID
AND X3.NAME_EN IS NULL
GROUP BY X5.COUNTRY_CD,X5.NAME_CN,X5.NAME_EN,X3.CITY_CD,X3.NAME_CN,X3.NAME_EN
ORDER BY COUNT_AIRPORT DESC;
--COUNTRY_NAME_EN=NULL 19
--CITY_CN_NAME=NULL 1
--CITY_EN_NAME=NULL 1501
Airport_Sat
import lombok.Data;
@Data
public class AirportSat
{
private String hub_id;
}
Airport_City_Link
import lombok.Data;
@Data
public class AirportCityLink
{
private String airport_hub_id;
private String city_hub_id;
}
City_Sat
import lombok.Data;
@Data
public class CitySat
{
private String hub_id;
private String city_cd;
private String name_cn;
private String name_en;
}
City_Country_Link
import lombok.Data;
@Data
public class CityCountryLink
{
private String country_hub_id;
private String city_hub_id;
}
Country_Sat
import lombok.Data;
@Data
public class CountrySat
{
private String hub_id;
private String country_cd;
private String name_cn;
private String name_en;
}
Flink_Csv