Python技術棧與Spark交叉資料分析雙向整合技術實戰--大資料ML樣本集案例實戰

開心雲技術社群發表於2018-12-17

版權宣告：本套技術專欄是作者（秦凱新）平時工作的總結和昇華，通過從真實商業環境抽取案例進行總結和分享，並給出商業應用的調優建議和叢集環境容量規劃等內容，請持續關注本套部落格。QQ郵箱地址：1120746959@qq.com，如有任何學術交流，可隨時聯絡。

1 Python Spark SQL 基本資料處理

Python技術棧與Spark交叉資料分析雙向整合技術實戰--大資料ML樣本集案例實戰

Python Spark DataFrame 基礎

 df = spark.read.parquet('/sql/users.parquet')
 df.show()
 
 +------+--------------+----------------+
 |  name|favorite_color|favorite_numbers|
 +------+--------------+----------------+
 |Alyssa|          null|  [3, 9, 15, 20]|
 |   Ben|           red|              []|
 +------+--------------+----------------+
複製程式碼

Python Spark DataFrame 聚合統計

  CustomerID,Genre,Age,Annual Income (k$),Spending Score (1-100)
  0001,Male,19,15,39
  0002,Male,21,15,81
  0003,Female,20,16,6
  0004,Female,23,16,77
  0005,Female,31,17,40
  0006,Female,22,17,76
  
  df = spark.read.csv('/sql/customers.csv',header=True)
  df.printSchema()
  df.show()
  
  root
   |-- CustomerID: string (nullable = true)
   |-- Genre: string (nullable = true)
   |-- Age: string (nullable = true)
   |-- Annual Income (k$): string (nullable = true)
   |-- Spending Score (1-100): string (nullable = true)
  
  +----------+------+---+------------------+----------------------+
  |CustomerID| Genre|Age|Annual Income (k$)|Spending Score (1-100)|
  +----------+------+---+------------------+----------------------+
  |      0001|  Male| 19|                15|                    39|
  |      0002|  Male| 21|                15|                    81|
  |      0003|Female| 20|                16|                     6|
  |      0004|Female| 23|                16|                    77|
  |      0005|Female| 31|                17|                    40|
  |      0006|Female| 22|                17|                    76|
  |      0007|Female| 35|                18|                     6|
  |      0008|Female| 23|                18|                    94|
  |      0009|  Male| 64|                19|                     3|
  |      0010|Female| 30|                19|                    72|
  |      0011|  Male| 67|                19|                    14|
  |      0012|Female| 35|                19|                    99|
  |      0013|Female| 58|                20|                    15|
  |      0014|Female| 24|                20|                    77|
  |      0015|  Male| 37|                20|                    13|
  |      0016|  Male| 22|                20|                    79|
  |      0017|Female| 35|                21|                    35|
  |      0018|  Male| 20|                21|                    66|
  |      0019|  Male| 52|                23|                    29|
  |      0020|Female| 35|                23|                    98|
  +----------+------+---+------------------+----------------------+
  
  df.agg({"Age": "max","Annual Income (k$)":"mean","Spending Score (1-100)":"mean"}).show()
  
  +---------------------------+-----------------------+--------+
  |avg(Spending Score (1-100))|avg(Annual Income (k$))|max(Age)|
  +---------------------------+-----------------------+--------+
  |                       50.2|                  60.56|      70|
  +---------------------------+-----------------------+--------+
複製程式碼

alias(alias)為DataFrame定義一個別名,稍後再函式中就可以利用這個別名來做相關的運算，例如說自關聯Join:

  df1 = df.alias('cus1')
  type(df1)
  df2 = df.alias('cus2')
  df3 = df1.join(df2,col('cus1.CustomerId')==col('cus2.CustomerId'),'inner')
  df3.count()
  
  200
  
  +----------+------+---+------------------+----------------------+----------+------+---+------------------+----------------------+
  |CustomerID| Genre|Age|Annual Income (k$)|Spending Score (1-100)|CustomerID| Genre|Age|Annual Income (k$)|Spending Score (1-100)|
  +----------+------+---+------------------+----------------------+----------+------+---+------------------+----------------------+
  |      0001|  Male| 19|                15|                    39|      0001|  Male| 19|                15|                    39|
  |      0002|  Male| 21|                15|                    81|      0002|  Male| 21|                15|                    81|
  |      0003|Female| 20|                16|                     6|      0003|Female| 20|                16|                     6|
  |      0004|Female| 23|                16|                    77|      0004|Female| 23|                16|                    77|
  |      0005|Female| 31|                17|                    40|      0005|Female| 31|                17|                    40|
  |      0006|Female| 22|                17|                    76|      0006|Female| 22|                17|                    76|
  |      0007|Female| 35|                18|                     6|      0007|Female| 35|                18|                     6|
  |      0008|Female| 23|                18|                    94|      0008|Female| 23|                18|                    94|
  |      0009|  Male| 64|                19|                     3|      0009|  Male| 64|                19|                     3|
  |      0010|Female| 30|                19|                    72|      0010|Female| 30|                19|                    72|
  +----------+------+---+------------------+----------------------+----------+------+---+------------------+----------------------+
  only showing top 10 rows
複製程式碼

cache()，將DataFrame快取到StorageLevel對應的快取級別中，預設是 MEMORY_AND_DISK

  df = spark.read.csv('/sql/customers.csv',header=True)
  a = df.cache()
  a.show()
  
  +----------+------+---+------------------+----------------------+
  |CustomerID| Genre|Age|Annual Income (k$)|Spending Score (1-100)|
  +----------+------+---+------------------+----------------------+
  |      0001|  Male| 19|                15|                    39|
  |      0002|  Male| 21|                15|                    81|
  |      0003|Female| 20|                16|                     6|
  |      0004|Female| 23|                16|                    77|
  |      0005|Female| 31|                17|                    40|
  |      0006|Female| 22|                17|                    76|
  |      0007|Female| 35|                18|                     6|
  |      0008|Female| 23|                18|                    94|
  |      0009|  Male| 64|                19|                     3|
  |      0010|Female| 30|                19|                    72|
  |      0011|  Male| 67|                19|                    14|
  |      0012|Female| 35|                19|                    99|
複製程式碼

checkpoint(eager=True) 對DataFrame設定斷點，這個方法是Spark2.1引入的方法，這個方法的呼叫會斬斷在這個 DataFrame上的邏輯執行計劃，將前後的依賴關係持久化到checkpoint檔案中去。
```
  sc
  sc.setCheckpointDir('/datas/checkpoint')
  a.checkpoint()
  a.show()
複製程式碼
```

coalesce(numPartitions) 重分割槽演算法，傳入的引數是DataFrame的分割槽數量。

  注意通過read方法讀取檔案，建立的DataFrame預設的分割槽數為檔案的個數，即一個檔案對
  應一個分割槽，在分割槽數少於coalesce指定的分割槽數的時候，呼叫coalesce是不起作用的
  
  df = spark.read.csv('/sql/customers.csv',header=True)
  df.rdd.getNumPartitions()
  1
  
  spark.read.csv('/sql/customers.csv',header=True).coalesce(3).rdd.getNumPartitions()
  1
  
  df = spark.range(0,20,2,3)
  df.rdd.getNumPartitions()
  df.coalesce(2).rdd.getNumPartitions()
  2
複製程式碼

repartition(numPartitions, *cols)這個方法和coalesce(numPartitions) 方法一樣，都是對DataFrame進行重新的分割槽，但是repartition這個方法會使用hash演算法，在整個叢集中進行shuffle，效率較低。repartition方法不僅可以指定分割槽數，還可以指定按照哪些列來做分區。

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.rdd.getNumPartitions()
  1
  
  df2 = df.repartition(3)
  df2.rdd.getNumPartitions()
  3
  
  df2.columns
  df3 = df2.repartition(6,'Genre')
  df3.show(20)
  
  +----------+------+---+------------------+----------------------+
  |CustomerID| Genre|Age|Annual Income (k$)|Spending Score (1-100)|
  +----------+------+---+------------------+----------------------+
  |      0003|Female| 20|                16|                     6|
  |      0004|Female| 23|                16|                    77|
  |      0005|Female| 31|                17|                    40|
  |      0006|Female| 22|                17|                    76|
  |      0007|Female| 35|                18|                     6|
  |      0008|Female| 23|                18|                    94|
  |      0010|Female| 30|                19|                    72|
  |      0012|Female| 35|                19|                    99|
  |      0013|Female| 58|                20|                    15|
  
  df3.rdd.getNumPartitions()
  6
複製程式碼

colRegex(colName)用正規表示式的方式返回我們想要的列。

  df = spark.createDataFrame([("a", 1), ("b", 2), ("c", 3)], ["Col1", "a"])
  df.select(df.colRegex("`(Col1)?+.+`")).show()
  +---+
  |  a|
  +---+
  |  1|
  |  2|
  |  3|
  +---+
複製程式碼

collect()，返回DataFrame中的所有資料，注意資料量大了容易造成Driver節點記憶體溢出！

  df = spark.createDataFrame([("a", 1), ("b", 2), ("c", 3)], ["Col1", "a"])
  df.collect()
  [Row(Col1='a', a=1), Row(Col1='b', a=2), Row(Col1='c', a=3)]
複製程式碼

columns,以列表的形式返回DataFrame的所有列名

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.columns
  
  df = spark.read.csv('/sql/customers.csv',header=True)
  df.columns
  ['CustomerID', 'Genre', 'Age', 'Annual Income (k$)', 'Spending Score (1-100)']
複製程式碼

SparkSQL DataFrame 轉換為 PandasDataFrame

  df = spark.read.csv('/sql/customers.csv',header=True)
  pdf = df.toPandas()
複製程式碼

Pandas 相關資料處理操作

  pdf.info()
  
  <class 'pandas.core.frame.DataFrame'>
  RangeIndex: 200 entries, 0 to 199
  Data columns (total 5 columns):
  CustomerID                200 non-null object
  Genre                     200 non-null object
  Age                       200 non-null object
  Annual Income (k$)        200 non-null object
  Spending Score (1-100)    200 non-null object
  dtypes: object(5)
  memory usage: 7.9+ KB
  
  pdf['Age'] = pdf['Age'].astype('int')
  pdf["Annual Income (k$)"]=pdf["Annual Income (k$)"].astype('int')
  pdf["Spending Score (1-100)"]=pdf["Spending Score (1-100)"].astype('int')
  pdf.info()
  
  <class 'pandas.core.frame.DataFrame'>
  RangeIndex: 200 entries, 0 to 199
  Data columns (total 5 columns):
  CustomerID                200 non-null object
  Genre                     200 non-null object
  Age                       200 non-null int64
  Annual Income (k$)        200 non-null int64
  Spending Score (1-100)    200 non-null int64
  dtypes: int64(3), object(2)
  memory usage: 7.9+ KB
複製程式碼

PandasDataFrame 轉換為 SparkSQL DataFrame

  df1 = spark.createDataFrame(pdf)
  df1.corr("Age","Annual Income (k$)")
  df1.corr("Spending Score (1-100)","Annual Income (k$)")
  
  0.009902848094037492
複製程式碼

count()返回DataFrame中Row的數量

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.count()
  
  200
複製程式碼

createGlobalTempView(name)使用DataFrame建立一個全域性的臨時表，其生命週期和啟動的app的週期一致，即啟動的spark應用存在則這個臨時的表就一直能訪問。直到 sparkcontext的stop方法的呼叫退出應用為止。建立的臨時表儲存在global_temp這個庫中。

  df = spark.read.csv('/sql/customers.csv',header=True)
  #df.createGlobalTempView('TT')
  spark.sql('select *  from global_temp.TT').show()
  
  +----------+------+---+------------------+----------------------+
  |CustomerID| Genre|Age|Annual Income (k$)|Spending Score (1-100)|
  +----------+------+---+------------------+----------------------+
  |      0001|  Male| 19|                15|                    39|
  |      0002|  Male| 21|                15|                    81|
  |      0003|Female| 20|                16|                     6|
  |      0004|Female| 23|                16|                    77|
  |      0005|Female| 31|                17|                    40|
  |      0006|Female| 22|                17|                    76|
  |      0007|Female| 35|                18|                     6|
複製程式碼

createOrReplaceGlobalTempView(name)上面的方法當遇到已經建立了的臨時表名的話會報錯，而這個方法遇到已經存在的臨時表會進行替換，沒有則建立。

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.createOrReplaceGlobalTempView('TT')
  spark.sql('select * from global_temp.TT').show()
  
  +----------+------+---+------------------+----------------------+
  |CustomerID| Genre|Age|Annual Income (k$)|Spending Score (1-100)|
  +----------+------+---+------------------+----------------------+
  |      0001|  Male| 19|                15|                    39|
  |      0002|  Male| 21|                15|                    81|
  |      0003|Female| 20|                16|                     6|
  |      0004|Female| 23|                16|                    77|
  |      0005|Female| 31|                17|                    40|
  |      0006|Female| 22|                17|                    76|
複製程式碼

crossJoin(other)返回兩個DataFrame的笛卡爾積組合。不要輕易嘗試這個方法，非常耗時且費資源

  df1 = spark.createDataFrame([('regan',27),('ting',24)],schema=['name','age'])
  df2 = spark.createDataFrame([('regan',65),('ting',48)],schema=['name','weight'])
  df3 = df1.coalesce(3).crossJoin(df2.coalesce(3))
  df3.show()
  
  +-----+---+-----+------+
  | name|age| name|weight|
  +-----+---+-----+------+
  |regan| 27|regan|    65|
  |regan| 27| ting|    48|
  | ting| 24|regan|    65|
  | ting| 24| ting|    48|
  +-----+---+-----+------+
複製程式碼

cube(*cols)在當前的DataFrame上建立多維的資料立方體

  from pyspark.sql.functions import *
  df = spark.read.csv('/sql/customers.csv',header=True)
  df.cube('Age','Genre').count().orderBy(desc("count"), asc("Age")).show()
  
  +----+------+-----+
  | Age| Genre|count|
  +----+------+-----+
  |null|  null|  200|
  |null|Female|  112|
  |null|  Male|   88|
  |  32|  null|   11|
  |  35|  null|    9|
  |  19|  null|    8|
  |  31|  null|    8|
  |  30|  null|    7|
  |  31|Female|    7|
  |  49|  null|    7|
  |  19|  Male|    6|
  |  23|Female|    6|
  |  23|  null|    6|
  |  27|  null|    6|
  |  32|Female|    6|
  |  35|Female|    6|
  |  36|  null|    6|
  |  38|  null|    6|
  |  40|  null|    6|
  |  47|  null|    6|
  +----+------+-----+
  only showing top 20 rows
複製程式碼

describe(*cols)統計cols對應的基本的統計資訊,包括數量、最大值、最小值、均值及標準差

  df = spark.read.csv('/sql/customers.csv',header=True)
  #df.describe('Age')
  df.describe('Age','Genre').show()
  
  +-------+-----------------+------+
  |summary|              Age| Genre|
  +-------+-----------------+------+
  |  count|              200|   200|
  |   mean|            38.85|  null|
  | stddev|13.96900733155888|  null|
  |    min|               18|Female|
  |    max|               70|  Male|
  +-------+-----------------+------+
  
  df.describe().show()
  
  +-------+------------------+------+-----------------+------------------+----------------------+
  |summary|        CustomerID| Genre|              Age|Annual Income (k$)|Spending Score (1-100)|
  +-------+------------------+------+-----------------+------------------+----------------------+
  |  count|               200|   200|              200|               200|                   200|
  |   mean|             100.5|  null|            38.85|             60.56|                  50.2|
  | stddev|57.879184513951124|  null|13.96900733155888| 26.26472116527124|    25.823521668370173|
  |    min|              0001|Female|               18|               101|                     1|
  |    max|              0200|  Male|               70|                99|                    99|
  +-------+------------------+------+-----------------+------------------+----------------------+
  
  pdf=df.toPandas()
  pdf.describe()
複製程式碼

distinct()返回DataFrame中非重複的資料

  df = spark.createDataFrame([(1,1),(1,2),(1,2),(5,5)])
  df.count()
  df.distinct().count()
  
  df = spark.createDataFrame([(1,1),(1,2),(1,2),(5,5)])
  df.count()
  4 
  df.distinct().count()
  3
複製程式碼

drop(*cols)按照列名刪除DataFrame中的列，返回新的DataFrame

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.columns
  ['CustomerID', 'Genre', 'Age', 'Annual Income (k$)', 'Spending Score (1-100)']
  
  df1 = df.drop('Age')
  df1.columns
  ['CustomerID', 'Genre', 'Annual Income (k$)', 'Spending Score (1-100)']
複製程式碼

dropDuplicates(subset=None)刪除重複行，subset用於指定在刪除重複行的時候考慮那幾列。

  from pyspark.sql import Row
  df = sc.parallelize([
  Row(name='regan', age=27, height=170),
  Row(name='regan', age=27, height=170),
  Row(name='regan', age=27, height=155)],3).toDF()
  df.show()
  
  +---+------+-----+
  |age|height| name|
  +---+------+-----+
  | 27|   170|regan|
  | 27|   170|regan|
  | 27|   155|regan|
  +---+------+-----+
  
  df.dropDuplicates().show()
  +---+------+-----+
  |age|height| name|
  +---+------+-----+
  | 27|   155|regan|
  | 27|   170|regan|
  +---+------+-----+
  
  df.dropDuplicates(subset=['age','name']).show()
  +---+------+-----+
  |age|height| name|
  +---+------+-----+
  | 27|   170|regan|
  +---+------+-----+
複製程式碼

2 Python Spark SQL 資料高階處理

numpy自由引入

dropna(how='any', thresh=None, subset=None)刪除DataFrame中的na資料，關鍵字參數how指定如何刪，有“any”和‘all’兩種選項，thresh指定行中na資料有多少個時刪除整行數據，這個設定將覆蓋how關鍵字引數的設定，subset指定在那幾列中刪除na資料。

 import numpy as np
 df = spark.createDataFrame([(np.nan,27.,170.),(44.,27.,170.),
 (np.nan,np.nan,170.)],schema=['luck','age','weight'])
 df.show()
 
 +----+----+------+
 |luck| age|weight|
 +----+----+------+
 | NaN|27.0| 170.0|
 |44.0|27.0| 170.0|
 | NaN| NaN| 170.0|
 +----+----+------+
 
 df.dropna(how='any').show()
 +----+----+------+
 |luck| age|weight|
 +----+----+------+
 |44.0|27.0| 170.0|
 +----+----+------+
 
 df.dropna(how='all').show()
 +----+----+------+
 |luck| age|weight|
 +----+----+------+
 | NaN|27.0| 170.0|
 |44.0|27.0| 170.0|
 | NaN| NaN| 170.0|
 +----+----+------+
 
 df.dropna(thresh=2).show()
 +----+----+------+
 |luck| age|weight|
 +----+----+------+
 | NaN|27.0| 170.0|
 |44.0|27.0| 170.0|
 +----+----+------+
複製程式碼

dtypes返回DataFrame列的名字及對應的資料型別組成的tuple列表

  import numpy as np
  df = spark.createDataFrame([(np.nan,27.,170.),(44.,27.,170.),
  (np.nan,np.nan,170.)],schema=['luck','age','weight'])
  df.dtypes
  
  [('luck', 'double'), ('age', 'double'), ('weight', 'double')]
複製程式碼

fillna(value, subset=None)用於DataFrame中空資料的填充。

  import numpy as np
  f = spark.createDataFrame([(np.nan,27.,170.),(44.,27.,170.),
  (np.nan,np.nan,170.)],schema=['luck','age','weight']).show()
  
  +----+----+------+
  |luck| age|weight|
  +----+----+------+
  | NaN|27.0| 170.0|
  |44.0|27.0| 170.0|
  | NaN| NaN| 170.0|
  +----+----+------+
  
  df.na.fill(0.0).show()
  +----+----+------+
  |luck| age|weight|
  +----+----+------+
  | 0.0|27.0| 170.0|
  |44.0|27.0| 170.0|
  | 0.0| 0.0| 170.0|
  +----+----+------+
  
  df.fillna(0.0).show()
  +----+----+------+
  |luck| age|weight|
  +----+----+------+
  | 0.0|27.0| 170.0|
  |44.0|27.0| 170.0|
  | 0.0| 0.0| 170.0|
  +----+----+------+
  
  df.na.fill(False).show()
  +----+----+------+
  |luck| age|weight|
  +----+----+------+
  | NaN|27.0| 170.0|
  |44.0|27.0| 170.0|
  | NaN| NaN| 170.0|
  +----+----+------+
  
  df.na.fill({'luck':0.0,'age':50.0}).show()
  +----+----+------+
  |luck| age|weight|
  +----+----+------+
  | 0.0|27.0| 170.0|
  |44.0|27.0| 170.0|
  | 0.0|50.0| 170.0|
  +----+----+------+
複製程式碼

filter(condition)按照傳入的條件進行過濾，其實where方法就是filter方法的一個別名而已。

  import numpy as np
  df = spark.createDataFrame([(np.nan,27.,170.),(44.,27.,170.),
  (np.nan,np.nan,170.)],schema=['luck','age','weight'])
  df.filter(df.luck != np.nan).show()
  
  +----+----+------+
  |luck| age|weight|
  +----+----+------+
  |44.0|27.0| 170.0|
  +----+----+------+

  import numpy as np
  df = spark.createDataFrame([(np.nan,27.,170.),(44.,27.,170.),
  (np.nan,np.nan,170.)],schema=['luck','age','weight'])
  df.filter('luck <> "NaN" ').show()
  
  +----+----+------+
  |luck| age|weight|
  +----+----+------+
  |44.0|27.0| 170.0|
  +----+----+------+
複製程式碼

first()返回DataFrame的第一條記錄

  import numpy as np
  df = spark.createDataFrame([(np.nan,27.,170.),(44.,27.,170.),
  (np.nan,np.nan,170.)],schema=['luck','age','weight'])
  df.show()
  df.first()
  
  Row(luck=nan, age=27.0, weight=170.0)
複製程式碼

foreach(f)，在每一個Row上運用f方法，實際上它呼叫的是df.rdd.foreach這個機遇 RDD上的foreach方法。（測試未通過）

  import numpy as np
  df = spark.createDataFrame([(np.nan,27.,170.),(44.,27.,170.),
  (np.nan,np.nan,170.)],schema=['luck','age','weight'])
  def myprint(x):
      print(x.age)
  df.foreach(lambda x:print(x))
  
  def pprint(x):
      for p in x:
          print(p.luck)
  df.foreachPartition(pprint)
複製程式碼

groupBy(*cols)使用給定的列進行分組，返回GroupedData物件

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.columns
  ['CustomerID', 'Genre', 'Age', 'Annual Income (k$)', 'Spending Score (1-100)']
  
  df.groupby('Genre').agg({'Age':'mean'}).show()
  +------+------------------+
  | Genre|          avg(Age)|
  +------+------------------+
  |Female|38.098214285714285|
  |  Male| 39.80681818181818|
  +------+------------------+
複製程式碼

head(n=None)返回DataFrame前n行資料，預設是返回1行，可以通過n關鍵字引數指定
```
  df = spark.read.csv('/sql/customers.csv',header=True)
  df.head(6)
複製程式碼
```

hint(name, *parameters),hint方法用於兩個DataFrame做Join操作的時候，指定Join的方式，一般為broadcast的方式。hint是暗示的意思，可以看出作者還是挺幽默的，給程式一個暗示，按照那種方式join。

  df1 = spark.createDataFrame([('regan',23),('ting',24)],schema=['name','age'])
  df2 = spark.createDataFrame([('regan',130),('ting',90)],schema=['name','weight'])
  df3 = df1.join(df2.hint('broadcast'),'name').show()
  
  +-----+---+------+
  | name|age|weight|
  +-----+---+------+
  |regan| 23|   130|
  | ting| 24|    90|
  +-----+---+------+
複製程式碼

intersect(other)返回兩個DataFrame的交集是集合中的概念

  df1 = spark.createDataFrame([('regan',23),('ting',24)],schema=['name','age'])
  df2 = spark.createDataFrame([('regan',23),('ting',90)],schema=['name','age'])
  df3 = df1.intersect(df2).show()
  
  +-----+---+
  | name|age|
  +-----+---+
  |regan| 23|
  +-----+---+
複製程式碼

join(other, on=None, how=None)，用來對兩個DataFrame做連線關聯操作，other是另外一個DataFrame,on指定以哪個欄位做關聯，how指定怎麼關聯，有 inner, cross, outer, full, full_outer, left, left_outer, right, right_outer, left_semi, and left_anti選項，預設是inner。

  df1 = spark.createDataFrame([('regan',23),('ting',24)],schema=['name','age'])
  df2 = spark.createDataFrame([('regan',130),('ting2',90)],schema=['name','weight'])
  df1.join(df2,on='name',how='left_outer').show()
  +-----+---+------+
  | name|age|weight|
  +-----+---+------+
  |regan| 23|   130|
  | ting| 24|  null|
  +-----+---+------+
  
  df1.join(df2,on='name',how='right_outer').show()
  +-----+----+------+
  | name| age|weight|
  +-----+----+------+
  |regan|  23|   130|
  |ting2|null|    90|
  +-----+----+------+
  
  df1.join(df2,on='name',how='left_semi').show()
  +-----+---+
  | name|age|
  +-----+---+
  |regan| 23|
  +-----+---+
  df1.join(df2,on='name',how='left_anti').show()
  +----+---+
  |name|age|
  +----+---+
  |ting| 24|
  +----+---+
複製程式碼

limit(num)限制返回的資料的條數,防止返回到driver節點的資料過大造成OOM

  df1 = spark.createDataFrame([('regan',23),('ting',24)],schema=['name','age'])
  df1.limit(1).collect()
複製程式碼

orderBy(*cols, **kwargs),返回按照指定列排好序的新的DataFrame。

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.orderBy('Age').show(3)
  df.orderBy('Age',ascending=False).show(3)
  
  +----------+-----+---+------------------+----------------------+
  |CustomerID|Genre|Age|Annual Income (k$)|Spending Score (1-100)|
  +----------+-----+---+------------------+----------------------+
  |      0034| Male| 18|                33|                    92|
  |      0066| Male| 18|                48|                    59|
  |      0092| Male| 18|                59|                    41|
  +----------+-----+---+------------------+----------------------+
  only showing top 3 rows
  
  +----------+-----+---+------------------+----------------------+
  |CustomerID|Genre|Age|Annual Income (k$)|Spending Score (1-100)|
  +----------+-----+---+------------------+----------------------+
  |      0061| Male| 70|                46|                    56|
  |      0071| Male| 70|                49|                    55|
  |      0058| Male| 69|                44|                    46|
  +----------+-----+---+------------------+----------------------+
  only showing top 3 rows
  
  df.orderBy(desc("Age")).show(3)
  df.orderBy(df.Age.desc()).show(3)
  
  orderBy方法和sort方法類似
  df.sort(desc("Age")).show(3)
  df.sort(df.Age.desc()).show(3)
複製程式碼

persist(storageLevel=StorageLevel(True, True, False, False, 1))用來指定DataFrame 的快取級別，預設為記憶體和磁碟。

  from pyspark import StorageLevel
  df = spark.read.csv('/sql/customers.csv',header=True)
  df.persist(storageLevel=StorageLevel.MEMORY_AND_DISK_2)
  DataFrame[CustomerID: string, Genre: string, Age: string, Annual Income (k$): string, Spending Score (1-100): string]
複製程式碼

randomSplit(weights, seed=None),按照給定的權重將DataFrame分為幾個 DataFrame,seed關鍵字引數用來指定隨機種子，用於復現結果。

      df = spark.range(0.,30.,2,3)
      df.show()
      df.describe().show()
      dfs = df.randomSplit([1.0,4.0],24)
      for df in dfs:
          df.show()
複製程式碼

rdd,返回DataFrame對應的RDD物件，利用這個物件可以呼叫RDD上的所有的方法，但是這些方法是比較底層的方法，在處理一些特殊任務的時候，頂層的DataFrame的方法可能無法解決，需要轉換到更底層的RDD上來進行操作。
```
  df = spark.range(0.,30.,2,3)
  rdd = df.rdd
  rdd.map(lambda x:x.id ** 2).collect()
複製程式碼
```

replace(to_replace, value=, subset=None)這個方法通過第一個引數指定要被替換掉的老的值，第二個引數指定新的值，subset關鍵字引數指定子集，預設是在整個 DataFrame上進行替換。把資料集中的99換成100

  注意上面在替換的過程中to_replace和value的型別必須要相同，而且to_replace資料型別只
  能是：bool, int, long, float, string, list or dict。value資料型別只能是： bool, int, long, float,
  string, list or None
  
  df = spark.read.csv('/sql/customers.csv',header=True)
  df.columns
  df.show()
  df2 = df.replace('99','100')
  df2.show()
  
  df.replace(['Female','Male'],['F','M'],'Genre').show()
  +----------+-----+---+------------------+----------------------+
  |CustomerID|Genre|Age|Annual Income (k$)|Spending Score (1-100)|
  +----------+-----+---+------------------+----------------------+
  |      0001|    M| 19|                15|                    39|
  |      0002|    M| 21|                15|                    81|
  |      0003|    F| 20|                16|                     6|
  |      0004|    F| 23|                16|                    77|
  |      0005|    F| 31|                17|                    40|
  |      0006|    F| 22|                17|                    76|
  |      0007|    F| 35|                18|                     6|
  |      0008|    F| 23|                18|                    94|
  |      0009|    M| 64|                19|                     3|
  |      0010|    F| 30|                19|                    72|
  |      0011|    M| 67|                19|                    14|
  |      0012|    F| 35|                19|                    99|
  |      0013|    F| 58|                20|                    15|
  |      0014|    F| 24|                20|                    77|
  |      0015|    M| 37|                20|                    13|
  |      0016|    M| 22|                20|                    79|
  |      0017|    F| 35|                21|                    35|
  |      0018|    M| 20|                21|                    66|
  |      0019|    M| 52|                23|                    29|
  |      0020|    F| 35|                23|                    98|
  +----------+-----+---+------------------+----------------------+
  df.na.replace(['Female','Male'],['F','M'],'Genre').show()
複製程式碼

rollup(*cols),按照指定的列名進行彙總，這樣就可以在彙總的資料集上運用聚合函式

  from pyspark.sql.functions import *
  df = spark.read.csv('/sql/customers.csv',header=True)
  df.rollup('Genre','Age').count().orderBy(desc('count'),'Genre').show()
  
  +------+----+-----+
  | Genre| Age|count|
  +------+----+-----+
  |  null|null|  200|
  |Female|null|  112|
  |  Male|null|   88|
  |Female|  31|    7|
  |Female|  23|    6|
  |Female|  49|    6|
  |Female|  32|    6|
  |Female|  35|    6|
  |  Male|  19|    6|
  |Female|  30|    5|
  |  Male|  32|    5|
  |  Male|  48|    5|
  |Female|  21|    4|
  |Female|  47|    4|
  |Female|  50|    4|
  |Female|  36|    4|
  |Female|  29|    4|
  |Female|  27|    4|
  |Female|  38|    4|
  |  Male|  59|    4|
  +------+----+-----+
複製程式碼

sample(withReplacement=None, fraction=None, seed=None),用於從DataFrame中進行取樣的方法，withReplacement關鍵字引數用於指定是否採用有放回的取樣，true為有放回採用，false為無放回的取樣，fraction指定取樣的比例，seed取樣種子，相同的種子對應的取樣總是相同的，用於場景的復現。
```
  df = spark.read.csv('/sql/customers.csv',header=True)
  df.count()
  200
  
  df2 = df.sample(withReplacement=True,fraction=0.2,seed=1)
  df2.count()
  35
複製程式碼
```

sampleBy(col, fractions, seed=None),按照指定的col列根據fractions指定的比例進行分層抽樣，seed是隨機種子，用於場景的復現。

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.sampleBy('Genre',{'Male':0.1,'Female':0.15}).groupBy('Genre').count().show()
  +------+-----+
  | Genre|count|
  +------+-----+
  |Female|   15|
  |  Male|   11|
  +------+-----+
複製程式碼

select(*cols),通過表示式選取DataFrame中符合條件的資料，返回新的DataFrame

  f = spark.read.csv('/sql/customers.csv',header=True)
  df.select('*').count()
  df.select('Age','Genre').show(10)
  df.select(df.Age.alias('age')).show(10)
複製程式碼

selectExpr(*expr),這個方法是select方法的一個變體，他可以接收一個SQL表示式，返回新的DataFrame

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.selectExpr('Age * 2','sqrt(Age)').show(10)
  
  df = spark.read.csv('/sql/customers.csv',header=True)
  df.selectExpr('Age * 2','sqrt(Age)').show(10)
  
  +---------+-------------------------+
  |(Age * 2)|SQRT(CAST(Age AS DOUBLE))|
  +---------+-------------------------+
  |     38.0|        4.358898943540674|
  |     42.0|         4.58257569495584|
  |     40.0|         4.47213595499958|
  |     46.0|        4.795831523312719|
  |     62.0|       5.5677643628300215|
  |     44.0|         4.69041575982343|
  |     70.0|        5.916079783099616|
  |     46.0|        4.795831523312719|
  |    128.0|                      8.0|
  |     60.0|        5.477225575051661|
  +---------+-------------------------+
複製程式碼

show(n=20, truncate=True, vertical=False),這個方法預設返回DataFrame的前20行記錄，可以通過truncate指定超過20個字元的記錄將會被截斷，vertical指定是否垂直顯示。
```
  df = spark.read.csv('/sql/customers.csv',header=True)
  df.selectExpr('Age * 2','sqrt(Age)').show(10,truncate=False,vertical=False)
複製程式碼
```
sortWithinPartitions(*cols, **kwargs)和sort(*cols, **kwargs)，這兩個方法都是用指定的cols列進行排序，通過kwargs引數指定升序降序。

sortWithinPartitions(*cols, **kwargs)和sort(*cols, **kwargs)，這兩個方法都是用指定的cols列進行排序，通過kwargs引數指定升序降序。

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.sort(['Age','Genre'],ascending=True).show(10)
  df.sort(df.Age.desc()).show(10)
  
  from pyspark.sql.functions import *
  df.sortWithinPartitions(['Age','Genre'],ascending=False).show(10)
  +----------+------+---+------------------+----------------------+
  |CustomerID| Genre|Age|Annual Income (k$)|Spending Score (1-100)|
  +----------+------+---+------------------+----------------------+
  |      0061|  Male| 70|                46|                    56|
  |      0071|  Male| 70|                49|                    55|
  |      0058|  Male| 69|                44|                    46|
  |      0109|  Male| 68|                63|                    43|
  |      0068|Female| 68|                48|                    48|
  |      0091|Female| 68|                59|                    55|
  |      0011|  Male| 67|                19|                    14|
  |      0083|  Male| 67|                54|                    41|
  |      0103|  Male| 67|                62|                    59|
  |      0063|Female| 67|                47|                    52|
  +----------+------+---+------------------+----------------------+
  df.sortWithinPartitions(desc('Age')).show(10)
複製程式碼

subtract(other),這個方法用來獲取在A集合裡而不再B集合裡的資料，返回新的 DataFrame

  df1 = spark.createDataFrame([('regan',),('ting',),('yu',)],schema=['name'])
  df2 = spark.createDataFrame([('regan',),('ting',),('sha',)],schema=['name'])
  df3 = df1.subtract(df2)
  df3.show()
複製程式碼

summary(*statistics),用傳入的統計方法返回概要資訊。不傳引數會預設計算count, mean, stddev, min, approximate quartiles (percentiles at 25%, 50%, and 75%), and max， *statistics引數可以是： count mean stddev min max arbitrary approximate percentiles

  f = spark.read.csv('/sql/customers.csv',header=True)
  df.summary().show()
  df.summary('min','count','75%').show()
  
  +-------+------------------+------+-----------------+------------------+----------------------+
  |summary|        CustomerID| Genre|              Age|Annual Income (k$)|Spending Score (1-100)|
  +-------+------------------+------+-----------------+------------------+----------------------+
  |  count|               200|   200|              200|               200|                   200|
  |   mean|             100.5|  null|            38.85|             60.56|                  50.2|
  | stddev|57.879184513951124|  null|13.96900733155888| 26.26472116527124|    25.823521668370173|
  |    min|              0001|Female|               18|               101|                     1|
  |    25%|              50.0|  null|             28.0|              40.0|                  34.0|
  |    50%|             100.0|  null|             36.0|              61.0|                  50.0|
  |    75%|             150.0|  null|             49.0|              78.0|                  73.0|
  |    max|              0200|  Male|               70|                99|                    99|
  +-------+------------------+------+-----------------+------------------+----------------------+
  
  +-------+----------+------+----+------------------+----------------------+
  |summary|CustomerID| Genre| Age|Annual Income (k$)|Spending Score (1-100)|
  +-------+----------+------+----+------------------+----------------------+
  |    min|      0001|Female|  18|               101|                     1|
  |  count|       200|   200| 200|               200|                   200|
  |    75%|     150.0|  null|49.0|              78.0|                  73.0|
  +-------+----------+------+----+------------------+----------------------+
複製程式碼

take(num),返回DataFrame的前num個Row資料組成的列表，注意num不要太大，容易造成driver節點的OOM。
```
  df = spark.read.csv('/sql/customers.csv',header=True)
  df.take(3)
複製程式碼
```

toDF(*cols)，返回新的帶有指定cols名字的DataFrame物件

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.columns
  
  df1 = df.toDF('id','sex','age','income','score')
  df1.columns
  df1.show(5)
複製程式碼

toJSON(use_unicode=True),將DataFrame中的Row物件轉換為json字串，預設使用 unicode編碼。toJSON方法返回的是RDD物件，而不是DataFrame物件。

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.show(5)
  df1 = df.toJSON()
  df1.collect()

  +----------+------+---+------------------+----------------------+
  |CustomerID| Genre|Age|Annual Income (k$)|Spending Score (1-100)|
  +----------+------+---+------------------+----------------------+
  |      0001|  Male| 19|                15|                    39|
  |      0002|  Male| 21|                15|                    81|
  |      0003|Female| 20|                16|                     6|
  |      0004|Female| 23|                16|                    77|
  |      0005|Female| 31|                17|                    40|
  +----------+------+---+------------------+----------------------+
  only showing top 5 rows
  
  ['{"CustomerID":"0001","Genre":"Male","Age":"19","Annual Income (k$)":"15","Spending Score (1-100)":"39"}',
   '{"CustomerID":"0002","Genre":"Male","Age":"21","Annual Income (k$)":"15","Spending Score (1-100)":"81"}',
   '{"CustomerID":"0003","Genre":"Female","Age":"20","Annual Income (k$)":"16","Spending Score (1-100)":"6"}',
   ......]
複製程式碼

toLocalIterator(),將DataFrame中所有資料返回為本地的可迭代的資料，資料量大了容易OOM。(除錯未通過)

  df = spark.read.csv('/sql/customers.csv',header=True)
  results = df.toLocalIterator()
  for data in results:
  print(data)
複製程式碼

toPandas(),將Spark中的DataFrame物件轉換為pandas中的DataFrame物件

  df = spark.read.csv('/sql/customers.csv',header=True)
  pan_df = df.toPandas()
  pan_df
  pan_df.head(10)
複製程式碼

union(other),返回兩個DataFrame的合集。

  df1 = spark.createDataFrame([('regan',),('ting',),('yu',)],schema=['name'])
  df2 = spark.createDataFrame([('regan',),('ting',),('sha',)],schema=['name'])
  
  +-----+
  | name|
  +-----+
  |regan|
  | ting|
  |   yu|
  |regan|
  | ting|
  |  sha|
  +-----+
複製程式碼

unionByName(other)根據名字來找出兩個DataFrame的合集，與欄位的順序沒關係，只要欄位名稱能對應上即可。

unpersist(blocking=False),這個方法用於將DataFrame上持久化的資料全部清除掉。

  df1 = spark.createDataFrame([('regan',11),('ting',1),('yu',2)],schema=['name','score'])
  df1.persist(storageLevel=StorageLevel.MEMORY_AND_DISK)
  df1.storageLevel
  df1.unpersist()
  df1.storageLevel
複製程式碼

where(condition),這個方法和filter方法類似。更具傳入的條件作出選擇。

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.where('Age >= 30').show()
複製程式碼

withColumn(colName, col),返回一個新的DataFrame，這個DataFrame中新增加 colName的列，或者原來本身就有colName的列，則替換掉。

  f = spark.read.csv('/sql/customers.csv',header=True)
  df.withColumn('Age',df.Age**2).show(10)
  df.withColumn('Age2',df.Age**2).show(10)


  +----------+------+------+------------------+----------------------+
  |CustomerID| Genre|   Age|Annual Income (k$)|Spending Score (1-100)|
  +----------+------+------+------------------+----------------------+
  |      0001|  Male| 361.0|                15|                    39|
  |      0002|  Male| 441.0|                15|                    81|
  |      0003|Female| 400.0|                16|                     6|
  |      0004|Female| 529.0|                16|                    77|
  |      0005|Female| 961.0|                17|                    40|
  |      0006|Female| 484.0|                17|                    76|
  |      0007|Female|1225.0|                18|                     6|
  |      0008|Female| 529.0|                18|                    94|
  |      0009|  Male|4096.0|                19|                     3|
  |      0010|Female| 900.0|                19|                    72|
  +----------+------+------+------------------+----------------------+
  only showing top 10 rows
  
  +----------+------+---+------------------+----------------------+------+
  |CustomerID| Genre|Age|Annual Income (k$)|Spending Score (1-100)|  Age2|
  +----------+------+---+------------------+----------------------+------+
  |      0001|  Male| 19|                15|                    39| 361.0|
  |      0002|  Male| 21|                15|                    81| 441.0|
  |      0003|Female| 20|                16|                     6| 400.0|
  |      0004|Female| 23|                16|                    77| 529.0|
  |      0005|Female| 31|                17|                    40| 961.0|
  |      0006|Female| 22|                17|                    76| 484.0|
  |      0007|Female| 35|                18|                     6|1225.0|
  |      0008|Female| 23|                18|                    94| 529.0|
  |      0009|  Male| 64|                19|                     3|4096.0|
  |      0010|Female| 30|                19|                    72| 900.0|
  +----------+------+---+------------------+----------------------+------+
  only showing top 10 rows
複製程式碼

withColumnRenamed(existing, new),對已經存在的列名重新命名為new，若名稱不存在則這個操作不做任何事情。

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.withColumnRenamed('Age','age').show(10)
  df.withColumnRenamed('Age2','age').show(10)

  +----------+------+---+------------------+----------------------+
  |CustomerID| Genre|age|Annual Income (k$)|Spending Score (1-100)|
  +----------+------+---+------------------+----------------------+
  |      0001|  Male| 19|                15|                    39|
  |      0002|  Male| 21|                15|                    81|
  |      0003|Female| 20|                16|                     6|
  |      0004|Female| 23|                16|                    77|
  |      0005|Female| 31|                17|                    40|
  |      0006|Female| 22|                17|                    76|
  |      0007|Female| 35|                18|                     6|
  |      0008|Female| 23|                18|                    94|
  |      0009|  Male| 64|                19|                     3|
  |      0010|Female| 30|                19|                    72|
  +----------+------+---+------------------+----------------------+
複製程式碼

write，藉助這個介面將DataFrame的內容儲存到外部的系統

  df = spark.read.csv('/sql/customers.csv',header=True)
  df.write
複製程式碼

3 Spark SQL 高階用法cube及上卷

group by:主要用來對查詢的結果進行分組，相同組合的分組條件在結果集中只顯示一行記錄。可以新增聚合函式。
grouping sets：對分組集中指定的組表示式的每個子集執行group by，group by A,B grouping sets(A,B)就等價於 group by A union group by B,其中A和B也可以是一個集合，比如group by A,B,C grouping sets((A,B),(A,C))。
rollup：在指定表示式的每個層次級別建立分組集。group by A,B,C with rollup首先會對(A、B、C)進行group by，然後對(A、B)進行group by，然後是(A)進行group by，最後對全表進行group by操作。

cube:為指定表示式集的每個可能組合建立分組集。首先會對(A、B、C)進行group by，然後依次是(A、B)，(A、C)，(A)，(B、C)，(B)，( C)，最後對全表進行group by操作。

  case class MemberOrderInfo(area:String,memberType:String,product:String,price:Int)
  
  import spark.implicits._
  
  val orders=Seq(
    MemberOrderInfo("深圳","鑽石會員","鑽石會員1個月",25),
    MemberOrderInfo("深圳","鑽石會員","鑽石會員1個月",25),
    MemberOrderInfo("深圳","鑽石會員","鑽石會員3個月",70),
    MemberOrderInfo("深圳","鑽石會員","鑽石會員12個月",300),
    MemberOrderInfo("深圳","鉑金會員","鉑金會員3個月",60),
    MemberOrderInfo("深圳","鉑金會員","鉑金會員3個月",60),
    MemberOrderInfo("深圳","鉑金會員","鉑金會員6個月",120),
    MemberOrderInfo("深圳","黃金會員","黃金會員1個月",15)
  )
 
 把seq轉換成DataFrame
 val memberDF:DataFrame =orders.toDF()
 把DataFrame註冊成臨時表
 memberDF.createOrReplaceGlobalTempView("orderTempTable")
 
 group by
 spark.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product").show
 
  +----+----------+--------+-----+                                                
  |area|memberType| product|total|
  +----+----------+--------+-----+
  |  深圳|      鑽石會員| 鑽石會員3個月|   70|
  |  深圳|      鑽石會員|鑽石會員12個月|  300|
  |  深圳|      鉑金會員| 鉑金會員6個月|  120|
  |  深圳|      鉑金會員| 鉑金會員3個月|  120|
  |  深圳|      鑽石會員| 鑽石會員1個月|   50|
  |  深圳|      黃金會員| 黃金會員1個月|   15|
  +----+----------+--------+-----+
 
 spark.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product grouping sets(area,memberType,product)").show
 
   +----+----------+--------+-----+                                                
  |area|memberType| product|total|
  +----+----------+--------+-----+
  |null|      null| 鉑金會員3個月|  120|
  |null|      鉑金會員|    null|  240|
  |null|      null|鑽石會員12個月|  300|
  |  深圳|      null|    null|  675|
  |null|      鑽石會員|    null|  420|
  |null|      null| 鑽石會員1個月|   50|
  |null|      null| 黃金會員1個月|   15|
  |null|      null| 鑽石會員3個月|   70|
  |null|      黃金會員|    null|   15|
  |null|      null| 鉑金會員6個月|  120|
  +----+----------+--------+-----+
 
 spark.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product grouping sets((area,memberType),(memberType,product))").show
 
  +----+----------+--------+-----+                                                
  |area|memberType| product|total|
  +----+----------+--------+-----+
  |null|      鉑金會員| 鉑金會員6個月|  120|
  |null|      鑽石會員|鑽石會員12個月|  300|
  |null|      鑽石會員| 鑽石會員3個月|   70|
  |  深圳|      鑽石會員|    null|  420|
  |null|      鉑金會員| 鉑金會員3個月|  120|
  |null|      黃金會員| 黃金會員1個月|   15|
  |null|      鑽石會員| 鑽石會員1個月|   50|
  |  深圳|      黃金會員|    null|   15|
  |  深圳|      鉑金會員|    null|  240|
  +----+----------+--------+-----+
 
 spark.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product with rollup").show
 
  +----+----------+--------+-----+                                                
  |area|memberType| product|total|
  +----+----------+--------+-----+
  |  深圳|      鑽石會員| 鑽石會員1個月|   50|
  |  深圳|      鑽石會員|鑽石會員12個月|  300|
  |  深圳|      鉑金會員| 鉑金會員3個月|  120|
  |  深圳|      鑽石會員|    null|  420|
  |  深圳|      null|    null|  675|
  |null|      null|    null|  675|
  |  深圳|      鑽石會員| 鑽石會員3個月|   70|
  |  深圳|      黃金會員| 黃金會員1個月|   15|
  |  深圳|      黃金會員|    null|   15|
  |  深圳|      鉑金會員|    null|  240|
  |  深圳|      鉑金會員| 鉑金會員6個月|  120|
  +----+----------+--------+-----+
  
  
  spark.sql("select area,memberType,product,sum(price) as total from orderTempTable group by area,memberType,product with cube").show
  
  +----+----------+--------+-----+
  |area|memberType| product|total|
  +----+----------+--------+-----+
  |  深圳|      null| 黃金會員1個月|   15|
  |null|      null| 鉑金會員3個月|  120|
  |  深圳|      null| 鉑金會員6個月|  120|
  |null|      鉑金會員| 鉑金會員6個月|  120|
  |null|      鉑金會員|    null|  240|
  |  深圳|      鑽石會員| 鑽石會員1個月|   50|
  |  深圳|      null| 鑽石會員1個月|   50|
  |null|      鑽石會員|鑽石會員12個月|  300|
  |  深圳|      鑽石會員|鑽石會員12個月|  300|
  |  深圳|      鉑金會員| 鉑金會員3個月|  120|
  |null|      鑽石會員| 鑽石會員3個月|   70|
  |  深圳|      鑽石會員|    null|  420|
  |null|      null|鑽石會員12個月|  300|
  |  深圳|      null|    null|  675|
  |null|      鉑金會員| 鉑金會員3個月|  120|
  |null|      鑽石會員|    null|  420|
  |null|      黃金會員| 黃金會員1個月|   15|
  |null|      鑽石會員| 鑽石會員1個月|   50|
  |null|      null| 鑽石會員1個月|   50|
  |null|      null|    null|  675|
  +----+----------+--------+-----+
複製程式碼

版權宣告：本套技術專欄是作者（秦凱新）平時工作的總結和昇華，通過從真實商業環境抽取案例進行總結和分享，並給出商業應用的調優建議和叢集環境容量規劃等內容，請持續關注本套部落格。QQ郵箱地址：1120746959@qq.com，如有任何學術交流，可隨時聯絡。

4 總結

Python技術棧與Spark交叉資料分析雙向整合，讓我們在大資料融合分析達到了通用，可以發現Spark SQL 其實很大部分功能和Pandas雷同

秦凱新於深圳 201812172352

Python技術棧與Spark交叉資料分析雙向整合技術實戰--大資料ML樣本集案例實戰

1 Python Spark SQL 基本資料處理

2 Python Spark SQL 資料高階處理

3 Spark SQL 高階用法cube及上卷

4 總結

相關文章